CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Robert
Robert

Reputation: 51

How to retrieve "last" non-zero values from multiple columns?

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.

Upvotes: 5

Views: 794

Answers (3)

DhruvJoshi
DhruvJoshi

Reputation: 17126

You can use something like below. 1. logic is to first unpivot values and then remove 0 entries and then calculate last non zero value as row_num=1.

  1. Followed by pivoting again to get the result.

Query is 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 working demo

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81960

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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()).

Upvotes: 1

Related Questions