Geek Answers Handbook

T-SQL - Returns the right non-zero column

I have a weird scenario where I need to return a right 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 expect the output to be:

GL           Amount
1            100
2            250
3            1000

Is there anyway to do this as a set-based approach without resorting to a CASE statement or similar solution? Performance will be important here.

+5
source share
5 answers
SELECT
   GL,
   COALESCE( NULLIF(Q4,0), NULLIF(Q3,0), NULLIF(Q2,0), NULLIF(Q1,0) ) as Amount
FROM
   myTable
+11
source

There is no SET-based approach since SQL is intended to be aggregated by row rather than column-wise.

I would really 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 NULL and COALESCE ...

COALESCE(NULLIF(Q4, 0), NULLIF(Q3, 0), NULLIF(Q2, 0), NULLIF(Q1, 0))
+4
source

. .

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

, , PIVOT, RowNumber. .

+1
source 
SELECT 
COALESCE(NULLIF(Q4,0),NULLIF(Q3,0),NULLIF(Q2,0),NULLIF(Q1,0))
+1
source

Install bash-ish based on alpha collation "Q?" (It is assumed that not all quarters are equal to 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
+1
source

More articles:


All Articles