CODEWITHSUNDEEP
sqlpivot-table
Nathan Currie
Nathan Currie

Reputation: 33

Selecting a max value from a pivot table

I built a result set using a pivot table that looks like this:

customer  A  B  C  D
Joe.      1  4  0  2
Sue.      2  0  3  9

How would I select the column name with the largest value from the pivot table? For instance:

Joe  B
Sue  D

Seems like it should be easy but I'll be damned if my skills aren't up to the task

Upvotes: 2

Views: 710

Answers (2)

Brian DeMilia
Brian DeMilia

Reputation: 13248

In SQL Server you can use UNPIVOT:

Fiddle with your data: http://sqlfiddle.com/#!3/f6601/12/0

select  customer, max(val)
from    (select * from tbl) x
        unpivot
        (val for col in(a, b, c, d)) y
group by customer

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269993

You can use a giant case statement:

select customer,
       (case when A >= B and A >= C and A >= D then 'A'
             when B >= C and B >= D then 'B'
             when C >= D then 'C'
             else 'D'
        end) as MaxCol
from table t;

However, it would probably be much easier to do this before you pivot the data rather than afterwards.

Upvotes: 3

Related Questions