2

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

juergen d
  • 201,996
  • 37
  • 293
  • 362
Nathan Currie
  • 33
  • 2

2 Answers2

3

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks, this worked. Guess I didn't want to write the giant case statement! There are actually quite a few more columns that I left out of the example, but this did do the trick. – Nathan Currie Aug 24 '14 at 02:41
  • @NathanCurrie if you're doing this with a lot of columns, ie 10+ or something of the like, I would use UNPIVOT since you have that option in sql server (as all you have to do is list the column names). – Brian DeMilia Aug 24 '14 at 02:50
3

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
Brian DeMilia
  • 13,103
  • 1
  • 23
  • 33