Get Last NOT NULL values from all the columns

sql-server, sql-server-2008, sql-server-2008-r2

Solution

Pretty much a straightforward `UNPIVOT` operation and an appropriate `ROW_NUMBER` to select the most recent value. The `UNPIVOT` automatically eliminates `NULL` values:

;With Numbered as (
     select *,ROW_NUMBER() OVER (PARTITION BY Col ORDER BY Dated desc) rn
     from @t
     unpivot (Value for Col in (Col1,Col2,Col3)) p
)
select * from Numbered where rn = 1

Result:

ID          Dated                   Value       Col      rn
----------- ----------------------- ----------- -------- --------------------
1           2013-12-09 00:00:00.000 20          Col1     1
1           2013-12-10 00:00:00.000 100         Col2     1
1           2013-12-08 00:00:00.000 300         Col3     1

Problem

I have a situation where I am trying to get last not null value from all the columns. here is some data to for demo puposes. Test Data ``` DECLARE @t TABLE (ID INT, Col1 INT, Col2 INT, Col3 INT, Dated DATETIME) INSERT INTO @t VALUES (1, NULL, 100 , NULL, '20131210'), --<-- Column2 from this row (1, 20 , 200 , NULL, '20131209'), --<-- Column1 from this row (1, 30 , NULL , 300 , '20131208'), --<-- Column3 from this row (1, 40 , 400 , NULL, '20131207') ╔════╦══════╦══════╦══════╦═════════════════════════╗ ║ ID ║ Col1 ║ Col2 ║ Col3 ║ Dated ║ ╠════╬══════╬══════╬══════╬═════════════════════════╣ ║ 1 ║ NULL ║ 100 ║ NULL ║ 2013-12-10 00:00:00.000 ║ ║ 1 ║ 20 ║ 200 ║ NULL ║ 2013-12-09 00:00:00.000 ║ ║ 1 ║ 30 ║ NULL ║ 300 ║ 2013-12-08 00:00:00.000 ║ ║ 1 ║ 40 ║ 400 ║ NULL ║ 2013-12-07 00:00:00.000 ║ ╚════╩══════╩══════╩══════╩═════════════════════════╝ ``` I have built a query which returns the required results. But was wondering if there is a more performance efficient way of doing this. Since this way I am quering the whole table each time I hope to find a more efficient way of getting these results. My Query ``` SELECT ColumnName, Col1 AS Value, Dated FROM ( SELECT TOP 1 'Column1' AS ColumnName , Col1 , Dated FROM @t WHERE Col1 IS NOT NULL ORDER BY Dated DESC )Q1 UNION ALL SELECT * FROM ( SELECT TOP 1 'Column2' AS ColumnName , Col2 , Dated FROM @t WHERE Col2 IS NOT NULL ORDER BY Dated DESC )Q2 UNION ALL SELECT * FROM ( SELECT TOP 1 'Column3' AS ColumnName , Col3 , Dated FROM @t WHERE Col3 IS NOT NULL ORDER BY Dated DESC )Q3 ``` Result Set ``` ╔════════════╦═══════╦═════════════════════════╗ ║ ColumnName ║ Value ║ Dated ║ ╠════════════╬═══════╬═════════════════════════╣ ║ Column1 ║ 20 ║ 2013-12-09 00:00:00.000 ║ ║ Column2 ║ 100 ║ 2013-12-10 00:00:00.000 ║ ║ Column3 ║ 300 ║ 2013-12-08 00:00:00.000 ║ ╚════════════╩═══════╩═════════════════════════╝ ``` It returns the correct results. but I am sure it can be done with a bit more simpler/efficient query . Any help or pointer is much appericiated

Original source