Update null column value from nonzero in previous line

Question

@@ Version 1

Using SQL Server 2008, I am trying to cascade values ​​by column. I have a table with a group identifier (GID) and Seq containing the ordering of the records in the group. For the columns present, in this case, Name and Salary - my real table contains more than 50 columns, if they contain NULL, I need to update the NULL value from the previous row for this column that contains a non-zero value.

Here are some things to illustrate:

GID Seq Name Salary 1 1 James NULL 1 2 NULL 100 1 3 NULL NULL 2 1 NULL 81 2 2 Smith NULL 2 3 NULL NULL 3 1 Charles NULL 3 2 NULL NULL 3 3 Brown NULL 3 4 NULL 75 4 0 Ron 50 4 1 NULL 20 4 2 NULL NULL 

My result should be:

 GID Seq Name Salary 1 1 James NULL 1 2 James 100 1 3 James 100 2 1 NULL 81 2 2 Smith 81 2 3 Smith 81 3 1 Charles NULL 3 2 Charles NULL 3 3 Brown NULL 3 4 Brown 75 4 0 Ron 50 4 1 Ron 20 4 2 Ron 20 

I want to do this without using dynamic SQL, loops or cursors.

Code for a simple test case:

 DECLARE @Test TABLE (GID int, Seq int, Name varchar(50), Salary decimal) INSERT INTO @Test VALUES (1, 1, 'James', NULL) INSERT INTO @Test VALUES (1, 2, NULL, 100.40) INSERT INTO @Test VALUES (1, 3, NULL, NULL) INSERT INTO @Test VALUES (2, 1, NULL, 80.50) INSERT INTO @Test VALUES (2, 2, 'Smith', NULL) INSERT INTO @Test VALUES (2, 3, NULL, NULL) INSERT INTO @Test VALUES (3, 1, 'Charles', NULL) INSERT INTO @Test VALUES (3, 2, NULL, NULL) INSERT INTO @Test VALUES (3, 3, 'Brown', NULL) INSERT INTO @Test VALUES (3, 4, NULL, 75) INSERT INTO @Test VALUES (4, 0, 'Ron', 50) INSERT INTO @Test VALUES (4, 1, NULL, 20) INSERT INTO @Test VALUES (4, 2, NULL, NULL) SELECT * FROM @Test 

@@ Version 2 Thanks to GilM for solving @@ Version 1. I made a small addition to the problem. The starting number in the Seq column can be either 0 or 1. In solving the first problem, the anchor in the recursive CTE refers to 1, what if its either 1 or 0? The last 3 lines of data (GID = 4) have been added to all three code codes in this version.

Thanks!

Answer 1

How about this ?:

 ;WITH CTE AS ( SELECT GID, SEQ, Name, Salary FROM @Test t1 WHERE SEQ = (SELECT MIN(SEQ) FROM @Test t2 WHERE t2.GID = t1.GID) UNION ALL SELECT t.GID, t.SEQ, COALESCE(t.Name,c.Name), COALESCE(t.Salary,c.Salary) FROM CTE c JOIN @Test t ON t.GID = c.GID AND t.SEQ = c.SEQ+1 ) UPDATE t SET Name = c.Name, Salary = c.Salary FROM @Test t JOIN CTE c ON c.GID = t.GID AND c.Seq = t.SEQ 

Answer 2

 update T set Name = ( select top(1) T1.Name from @Test as T1 where T1.GID = T.GID and T1.Seq <= T.Seq and T1.Name is not null order by T1.Seq desc ), Salary = ( select top(1) T1.Salary from @Test as T1 where T1.GID = T.GID and T1.Seq <= T.Seq and T1.Salary is not null order by T1.Seq desc ) from @Test as T where T.Name is null or T.Salary is null 

With 50 columns, there will be a lot of typing and many correlated subqueries.

Here is a version that uses XML instead. Less text input and performance may be better.

 with C as ( select GID, ( select * from @Test as T2 where T1.GID = T2.GID order by T2.Seq desc for xml path('row'), type ) as X from @Test as T1 group by GID ) update T set Name = CXvalue('(/row[Seq<=sql:column("T.Seq")]/Name)[1]', 'varchar(50)'), Salary = CXvalue('(/row[Seq<=sql:column("T.Seq")]/Salary)[1]', 'decimal') from @Test as T inner join C on T.GID = C.GID 

SE-Data