February 25, 2010 at 3:56 am
/****** Object: Table [dbo].[tblDemo] Script Date: 02/25/2010 10:41:14 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblDemo]') AND type in (N'U'))
DROP TABLE [dbo].[tblDemo]
GO
/****** Object: Table [dbo].[tblDemo] Script Date: 02/25/2010 10:41:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblDemo](
[pkId] [int] IDENTITY(1,1) NOT NULL,
[Col1] [int] NULL,
[Col2] [int] NULL,
[Col3] [int] NULL,
[Col4] [int] NULL,
CONSTRAINT [PK_tblDemo] PRIMARY KEY CLUSTERED
(
[pkId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[tblDemo]
([Col1],[Col2],[Col3],[Col4])
VALUES
(1,1,1,1)
GO
INSERT INTO [dbo].[tblDemo]
([Col1],[Col2],[Col3],[Col4])
VALUES
(2,2,2,NULL)
GO
INSERT INTO [dbo].[tblDemo]
([Col1],[Col2],[Col3],[Col4])
VALUES
(3,3,NULL,NULL)
GO
INSERT INTO [dbo].[tblDemo]
([Col1],[Col2],[Col3],[Col4])
VALUES
(4,NULL,NULL,NULL)
GO
INSERT INTO [dbo].[tblDemo]
([Col1],[Col2],[Col3],[Col4])
VALUES
(5,NULL,NULL,5)
GO
INSERT INTO [dbo].[tblDemo]
([Col1],[Col2],[Col3],[Col4])
VALUES
(6,NULL,6,NULL)
GO
INSERT INTO [dbo].[tblDemo]
([Col1],[Col2],[Col3],[Col4])
VALUES
(7,7,7,7)
GO
Select * from dbo.tblDemo
go
/* This is what we want as output
1,1,1,1,1
2,2,2,2,1
3,3,3,2,1
4,4,3,2,1
5,5,3,2,5
6,6,3,6,5
7,7,7,7,7
How can I do this SQL? CTE?
I know how to do it in TSQL using a cursor...
*/
February 25, 2010 at 4:48 am
This may give you required output....
DECLARE @tbl AS TABLE ( [pkId] int, [Col1] int, [Col2] int, [Col3] int, [Col4] int )
DECLARE @tbl1 AS TABLE ( [pkId] int, [Col1] int, [Col2] int, [Col3] int, [Col4] int )
DECLARE @i AS int
SET @i = (SELECT COUNT(*) FROM tbldemo )
INSERT INTO @tbl1
SELECT * FROM tbldemo
INSERT INTO @tbl
SELECT TOP 1 * FROM tbldemo ORDER BY PKID
WHILE @i > = 0
BEGIN
INSERT INTO @tbl
SELECT top 1 PKID,
CASE WHEN COL1 IS NULL THEN (SELECT Top 1 Col1 FROM @tbl ORDER BY PKID DESC ) ELSE Col1 END,
CASE WHEN COL2 IS NULL THEN (SELECT Top 1 Col2 FROM @tbl ORDER BY PKID DESC ) ELSE Col2 END,
CASE WHEN COL3 IS NULL THEN (SELECT Top 1 Col3 FROM @tbl ORDER BY PKID DESC ) ELSE Col3 END,
CASE WHEN COL4 IS NULL THEN (SELECT Top 1 Col4 FROM @tbl ORDER BY PKID DESC ) ELSE Col4 END
FROM @tbl1 ORDER BY PKID
SET @i = @i - 1
DELETE TOP(1) FROM @tbl1
END
SELECT * FROM @tbl
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
February 25, 2010 at 4:57 am
I am not sure that I understand your requirements clearly, can you elaborate more on how you arrived at the final output containing five columns?
--Ramesh
February 25, 2010 at 5:08 am
I want to keep the last NOT NULL value of a row until a new value is given
Regards
JH
February 25, 2010 at 5:11 am
Thats a good start thanks for the Info
Regards
JH
February 25, 2010 at 6:13 am
;with cte1 as
(
select pkid,isnull(col1,-99) as colval,'1' as col
from tblDemo
union all
select pkid,isnull(col2,-99) as col1,'2' as col
from tbldemo
union all
select pkid,isnull(col3,-99) as col1,'3' as col
from tbldemo
union all
select pkid,isnull(col4,-99) as col1,'4' as col
from tbldemo
)
,cte2 as
(
select *
from cte1
where colval <> -99
),
cte3 as
(
select col,pkid,max(colval) as colval,max(colval) as r1,
case when exists
(select 1
from cte2 c2
where c2.pkid>c1.pkid and c2.col = c1.col)
then (select top 1 c2.pkid - 1
from cte2 c2 where c2.pkid>c1.pkid and c2.col =c1.col)
else (select max(colval) from cte1 c2 where c2.col =c1.col)
end as r2
from cte2 c1
group by pkid,col
),
cte4 as
(
select col,pkid,
(select c2.colval from cte3 c2
where c2.r1<=c1.pkid and c2.r2>=c1.pkid and c2.col = c1.col) as colval2
from cte1 c1
group by col,pkid
)
select *
from cte4
pivot
(
max(colval2) for col in ([1],[2],[3],[4])
)P
Hope this works as a single query..
--Divya
February 25, 2010 at 6:34 am
Here is another way using OUTER APPLY:
Select t.pkId,
COALESCE( t.Col1, t1.Col1 ) AS Col1,
COALESCE( t.Col2, t2.Col2 ) AS Col2,
COALESCE( t.Col3, t3.Col3 ) AS Col3,
COALESCE( t.Col4, t4.Col4 ) AS Col4
from #tblDemo t
OUTER APPLY
(
SELECTTOP 1 t1.Col1
FROM#tblDemo t1
WHEREt.pkId > t1.pkId
AND t1.Col1 IS NOT NULL
AND t.Col1 IS NULL
ORDER BY t1.pkId DESC
) t1
OUTER APPLY
(
SELECTTOP 1 t1.Col2
FROM#tblDemo t1
WHEREt.pkId > t1.pkId
AND t1.Col2 IS NOT NULL
AND t.Col2 IS NULL
ORDER BY t1.pkId DESC
) t2
OUTER APPLY
(
SELECTTOP 1 t1.Col3
FROM#tblDemo t1
WHEREt.pkId > t1.pkId
AND t1.Col3 IS NOT NULL
AND t.Col3 IS NULL
ORDER BY t1.pkId DESC
) t3
OUTER APPLY
(
SELECTTOP 1 t1.Col4
FROM#tblDemo t1
WHEREt.pkId > t1.pkId
AND t1.Col4 IS NOT NULL
AND t.Col4 IS NULL
ORDER BY t1.pkId DESC
) t4
--Ramesh
February 25, 2010 at 6:39 am
Jacco
Assuming there are no gaps in your identity column:
;WITH Results AS (
SELECT pkId, Col1, Col2, Col3, Col4
FROM dbo.tblDemo
WHERE pkId = 1
UNION ALL
SELECT s.pkId,
ISNULL(s.Col1, r.Col1),
ISNULL(s.Col2, r.Col2),
ISNULL(s.Col3, r.Col3),
ISNULL(s.Col4, r.Col4)
FROM dbo.tblDemo s
INNER JOIN Results r ON s.pkId = r.pkId+1
)
SELECT * FROM Results
Edit: corrected table name
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 25, 2010 at 6:45 am
Chris Morris-439714 (2/25/2010)
JaccoAssuming there are no gaps in your identity column:
;WITH Results AS (SELECT pkId, Col1, Col2, Col3, Col4
FROM dbo.tblDemo
WHERE pkId = 1
UNION ALL
SELECT s.pkId,
ISNULL(s.Col1, r.Col1),
ISNULL(s.Col2, r.Col2),
ISNULL(s.Col3, r.Col3),
ISNULL(s.Col4, r.Col4)
FROM #tblDemo s
INNER JOIN Results r ON s.pkId = r.pkId+1
)
SELECT * FROM Results
Cheers
ChrisM
Good one Chris..
--Divya
February 25, 2010 at 6:49 am
Divya Agrawal (2/25/2010)
Good one Chris..
Thanks 🙂
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply