How to retrieve "last" non-zero 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 set of records 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 a SQL query (for SQL Server 2012) which returns the last (in time) non zero values for all columns, val1, val2, ..., that is,

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 at Subquery: how to retrieve the last non-zero value from a column? but it only works for one column and the generalisation to include more columns (as in this case) seems not practical.

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;

Generally, I am opposed to using select distinct as a replacement for an aggregation query. 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 the zero values last. If you can have negative values, then use abs(sign()).

Answer 2

Another option is a quick unpivot followed by a pivot

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