Update Nulls with information from previous record

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)

Update Nulls with information from previous record

Author

Topic

hockeyman9474
Starting Member

11 Posts

Posted - 2014-06-05 : 10:21:02

OK I have table with a car dealer name in a column 1 followed by a car line in column 2 followed by columns of other data. The Dealer column will have the dealers name in the first row, but not in the subsequent rows. In SQL I would like to update the NUL value with previous data. Logistically thinking, I would like to create a variable and fill it with some information. As long as the field is null, i update the field with the variable. If the field is something other than null or = to the value in the variable, then that value is the new variable. so here's how my table lloks now:

Dealer Carline Data1 Data2 Data3
Dealer1 carline1 1234 4321 4567
carline2 7894 1793 3179
Dealer2 carline1 1234 4321 4567
carline2 7894 1793 3179
Dealer3 carline1 1234 4321 4567
carline2 7894 1793 3179

How I would like it to look:
Dealer Carline Data1 Data2 Data3
Dealer1 carline1 1234 4321 4567
Dealer1 carline2 7894 1793 3179
Dealer2 carline1 1234 4321 4567
Dealer2 carline2 7894 1793 3179
Dealer3 carline1 1234 4321 4567
Dealer3 carline2 7894 1793 3179

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-05 : 11:34:40

How do you determine the order of the rows?

hockeyman9474
Starting Member

11 Posts

Posted - 2014-06-05 : 13:21:36

quote:
Originally posted by Lamprey

How do you determine the order of the rows?

we are a car distributor. We have 65 dealers spread amongst 6 districts 1-6. So it's Dit order first and then Dealer number order ascending on both

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-05 : 14:05:47

I don't see a "Dit" column. And Dealer is NULL for half of the sample data. So, how would you determine the order of the column in order to figure out which is a "previous" row?

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-06-06 : 02:26:37

The following should work



CREATE TABLE #test(Dealer varchar(MAX),Carline varchar(MAX),Data1 int,Data2 int,Data3 int)
INSERT INTO #test
SELECT 'Dealer1','carline1',1234,4321,4567 UNION ALL
SELECT NULL,'carline2',7894,1793,3179 UNION ALL
SELECT 'Dealer2','carline1',1234,4321,4567 UNION ALL
SELECT NULL,'carline2',7894,1793,3179 UNION ALL
SELECT 'Dealer3','carline1',1234,4321,4567 UNION ALL
SELECT  NULL,'carline2',7894,1793,3179
SELECT		CASE WHEN a.Dealer IS NULL THEN (SELECT Dealer FROM (SELECT *,RowNum = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM #test)x WHERE x.RowNum=a.RowNum-1)ELSE a.Dealer END AS Dealer
		 ,	Carline
		 ,	Data1
		 ,	Data2	
		 ,	Data3	
FROM (
SELECT *,RowNum = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM #test
)a
DROP TABLE #test

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources