please help with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

please help with query

Author

Topic

xclaim
Starting Member

8 Posts

Posted - 2014-05-13 : 08:22:11

INPUT data


no.	data
1	data1
2	
3	
4	data2
5	
6	data3
7	
8	
9	data4
10	data5

OUTPUT needed


no.	data
1	data1
2	data1
3	data1
4	data2
5	data2
6	data3
7	data3
8	data3
9	data4
10	data5

Basically all the nulls/blanks in the input data column need to be filled with data from the previous row

thanks in advance.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-13 : 09:23:10

[code]
;with aCTE
AS
(select 1 no, 'data1' data union all
select 2,null union all
select 3,null union all
select 4,'data2' union all
select 5, null union all
select 6,'data3' union all
select 7,null union all
select 8, null union all
select 9,'data4' union all
select 10,'data5' )

select A.no,B.data
from
aCTE AS A
outer apply
(
select top 1 B.data
from aCTE as B
WHERE a.NO >= b.no
AND B.data IS NOT NULL
ORDER BY B.no DESC )B
[/code]

output
[code]
no data
1 data1
2 data1
3 data1
4 data2
5 data2
6 data3
7 data3
8 data3
9 data4
10 data5
[/code]

sabinWeb MCP

xclaim
Starting Member

8 Posts

Posted - 2014-05-13 : 10:04:28

thanks stepson

i enhanced it a bit

SELECT 1      no, 
       'data1'data , null data_new
UNION ALL 
SELECT 2, 
       NULL , null
UNION ALL 
SELECT 3, 
       NULL , null
UNION ALL 
SELECT 4, 
       'data2' , 'data6'
UNION ALL 
SELECT 5, 
       NULL , null
UNION ALL 
SELECT 6, 
       'data3' , null
UNION ALL 
SELECT 7, 
       NULL , null
UNION ALL 
SELECT 8, 
       NULL , 'data7'
UNION ALL 
SELECT 9, 
       'data4' , null
UNION ALL 
SELECT 10, 
       'data5' , null

input now is


no	data	data_new
1	data1	
2		
3		
4	data2	data6
5		
6	data3	
7		
8		data7
9	data4	
10	data5

output should be

no	data	data_new
1	data1	
2	data1	
3	data1	
4	data2	data6
5	data2	data6
6	data3	data6
7	data3	data6
8	data3	data7
9	data4	data7
10	data5	data7

nulls are fine as well

thanks much

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-14 : 01:06:28

[code]
;with aCTE
AS
(SELECT 1 no, 'data1'data , null data_new UNION ALL
SELECT 2, NULL , null UNION ALL
SELECT 3, NULL , null UNION ALL
SELECT 4, 'data2' , 'data6' UNION ALL
SELECT 5, NULL , null UNION ALL
SELECT 6, 'data3' , null UNION ALL
SELECT 7, NULL , null UNION ALL
SELECT 8, NULL , 'data7' UNION ALL
SELECT 9, 'data4' , null UNION ALL
SELECT 10, 'data5' , null
)

SELECT
A.no
,B.data
,C.data_new
FROM
aCTE as A
outer apply
(
select top 1 B.data
from aCTE as B
WHERE a.NO >= b.no
AND B.data IS NOT NULL
ORDER BY B.no DESC )B
outer apply
(
select top 1 B.data_new
from aCTE as B
WHERE a.NO >= b.no
AND B.data_new IS NOT NULL
ORDER BY B.no DESC )C
[/code]

output:
[code]
no data data_new
1 data1 NULL
2 data1 NULL
3 data1 NULL
4 data2 data6
5 data2 data6
6 data3 data6
7 data3 data6
8 data3 data7
9 data4 data7
10 data5 data7
[/code]

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources