CODEWITHSUNDEEP
sqlt-sql
Patrick
Patrick

Reputation: 5846

T-SQL - Return right-most non-zero column

I have a strange scenario where I need to return the right-most non-zero column in a table structured as follows:

GL           Q1          Q2          Q3          Q4
1            100         0           0           0
2            100         900         250         0
3            600         100         0           1000

I am expecting the output to be:

GL           Amount
1            100
2            250
3            1000

Is there anyway to accomplish this as a set-based approach without having to resort to a CASE statement or similar solution? Performance is going to be important here.

Upvotes: 5

Views: 2098

Answers (5)

Chains
Chains

Reputation: 13157

SELECT
   GL,
   COALESCE( NULLIF(Q4,0), NULLIF(Q3,0), NULLIF(Q2,0), NULLIF(Q1,0) ) as Amount
FROM
   myTable

Upvotes: 11

Alex K.
Alex K.

Reputation: 175766

Set based-ish based on alpha sorting of "Q?" (Assumes not all quarters are 0)

with T as ( 
select GL, Q, VALUE, row_number() over (partition by GL order by Q desc) as row
    from (
        select GL,Q1,Q2,Q3,Q4 from theTable
    ) T
    unpivot (VALUE for Q in (Q1,Q2,Q3,Q4)) as U
    where VALUE <> 0 
)
select * from T
   where row = 1

Upvotes: 1

user610217
user610217

Reputation: 

SELECT 
COALESCE(NULLIF(Q4,0),NULLIF(Q3,0),NULLIF(Q2,0),NULLIF(Q1,0))

Upvotes: 1

Amy B
Amy B

Reputation: 110111

Case statement is correct to use here. It is the most performant option available.

SELECT GL,
  CASE 
    WHEN Q4 != 0 THEN Q4
    WHEN Q3 != 0 THEN Q3
    WHEN Q2 != 0 THEN Q2
    ELSE Q1
  END
FROM TheTable

If you require a set based approach - you'd have to PIVOT and then aggregate by RowNumber. That's slower.

Upvotes: 1

MatBailie
MatBailie

Reputation: 86716

There is no SET based approach, as SQL is designed to aggregate across rows, not columns.

I would actually expect CASE to be pretty fast here...

CASE WHEN Q4 <> 0 THEN Q4
     WHEN Q3 <> 0 THEN Q3
     WHEN Q2 <> 0 THEN Q2
     WHEN Q1 <> 0 THEN Q1
                  ELSE NULL
END

There is, however, an alternative using NULLs and COALESCE...

COALESCE(NULLIF(Q4, 0), NULLIF(Q3, 0), NULLIF(Q2, 0), NULLIF(Q1, 0))

Upvotes: 4

Related Questions