Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to get data where value is null Expand / Collapse
Author
Message
Posted Monday, December 17, 2012 10:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 3, 2014 10:46 AM
Points: 21, Visits: 61
Hello,
I have a table with data like below

id value
1 2
2 4
3 6
4 NULL
5 8
6 NULL
7 NULL
8 12
9 15
10 NULL
11 20
12 NULL

and i need output like below

id value
1 2
2 4
3 6
4 6
5 8
6 8
7 8
8 12
9 15
10 15
11 20
12 20

please help..
Post #1397556
Posted Tuesday, December 18, 2012 2:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 12:52 PM
Points: 13,636, Visits: 11,509
I managed to write some code that doesn't use a cursor, so hooray for performance

SELECT
ID = tmp.ID
,Value = t3.[Value]
FROM
(SELECT t1.[ID]
,MaxID = MAX(t2.ID)
FROM [dbo].[Test] t1
INNER JOIN [dbo].[Test] t2 ON [t1].[ID] >= [t2].[ID] AND t2.[Value] IS NOT NULL
GROUP BY t1.[ID]) tmp
INNER JOIN [dbo].[Test] t3 ON tmp.[MaxID] = t3.[ID]

Please read the link in my signature on how to post questions and sample data. It will make the life of people who are trying to answer your question easier.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1397625
Posted Tuesday, December 18, 2012 4:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 3, 2014 10:46 AM
Points: 21, Visits: 61
Thank You Very Much...

Its so helpful to me..
Post #1397670
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse