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

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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