How to get the "last" nonzero values ​​from multiple columns?

Question

Considering an ordered table with 11 columns:
timespan, val1, val2, val3, val4, val5, val6, val7, val8, val9 and val10

Suppose a recordset is like:

 timespan val1 val2 val3 val4 val5 val6 val7 val8 val9 val10 10/09/2011 0 0 60 80 40 0 0 40 80 0 10/10/2011 0 10 90 30 70 50 50 70 30 90 10/11/2011 10 0 20 0 0 60 60 0 0 20 

I need an SQL query (for SQL Server 2012) that returns the last (in time) nonzero values ​​for all columns, val1, val2, ..., i.e.

 val1 val2 val3 val4 val5 val6 val7 val8 val9 val10 10 10 20 30 70 60 60 70 30 20 

A similar question can be found in Subquery: how to get the last nonzero value from a column? , but it only works for one column, and generalizing to include more columns (as in this case) seems impractical.

Answer 1

You can use first_value() :

 select distinct first_value(val1) over (order by sign(val1) desc, timespan desc) as val1, first_value(val2) over (order by sign(val2) desc, timespan desc) as val2, . . . from t; 

In general, I am opposed to using select distinct as a replacement for an aggregation request. Unfortunately, SQL Server supports first_value() as a window function, but does not provide an equivalent for aggregation.

Note: the sign() function is used to put null values ​​at the end. If you can have negative values, use abs(sign()) .

Answer 2

Another option is a quick failure, followed by a summary

Example

 Select * From ( Select top 1 with ties item,value From YourTable UnPivot ( Value for Item in (val1,val2,val3,val4,val5,val6,val7,val8,val9,val10) ) u Where value<>0 Order by Row_Number() over (Partition By item Order by timespan desc) ) src Pivot (max(value) For item in (val1,val2,val3,val4,val5,val6,val7,val8,val9,val10) ) p 

Returns

 val1 val2 val3 val4 val5 val6 val7 val8 val9 val10 10 10 20 30 70 60 60 70 30 20 

Answer 3

You can use something like below. 1. The logic is to reset the values ​​first, then delete 0 records, and then calculate the last non-zero value as row_num = 1.

2. Then a turn follows to get the result.

Request below

 create table t (timespan date,val1 int,val2 int,val3 int,val4 int,val5 int,val6 int,val7 int,val8 int,val9 int,val10 int); insert into t values ('10/09/2011', 0, 0,60,80,40, 0, 0,40,80, 0) ,('10/10/2011', 0,10,90,30,70,50,50,70,30,90) ,('10/11/2011',10, 0,20, 0, 0,60,60, 0, 0,20); select * from ( select value, Columns from ( select timespan, value, Columns, row_number() over(partition by Columns order by timespan desc) r from (select * from t)s unpivot ( value for Columns in ([val1],[val2],[val3],[val4],[val5],[val6],[val7],[val8],[val9],[val10]) )up where value<>0 ) t where r=1 )s pivot ( max(value) for Columns in ([val1],[val2],[val3],[val4],[val5],[val6],[val7],[val8],[val9],[val10]) )p 

See a working demo