SQL: Identifying which column has greatest value in each row

I have a table with columns ID, A, B, C, D, E... (10 numeric columns in all)

For each row, I need to find which column has the largest value, and what that value is.

开发者_C百科

E.G. Here's 1 row of my table:

ID A B C D E F G H I J

XY 5 4 9 5 0 1 3 2 1 7

I want to generate 2 new columns:

maxvalue, which would equal 9, and maxcol, which would equal "C"

Any advice, beyond a massive IF statement?

---

I don't have a sql processor to hand, but something along the lines of

select id , colName
from 
(select id, 'A' as colName, a as value union all
select id, 'B' as colName, b as value union all
select id, 'C' as colName, c as value union all
select id, 'D' as colName, d as value union all
select id, 'E' as colName, e as value union all
select id, 'F' as colName, f as value union all
select id, 'G' as colName, g as value union all
select id, 'H' as colName, h as value union all
select id, 'I' as colName, i as value union all
select id, 'J' as colName, j as value)
group by id having max(value)

---

A solution using unpivot assuming your table is named "TestTable":

WITH unpivoted as 
(
    SELECT * FROM TestTable
    UNPIVOT
    (
        Val FOR Col IN (A, B, C, D, E, F, G, H, I, J)
    ) as u
),
maxvals as (
    SELECT ID, max(val) as MaxVal 
    FROM unpivoted
    GROUP BY ID
)
SELECT
    TestTable.*, 
    MaxVals.MaxVal, 
    (SELECT top 1 Col 
     FROM unpivoted 
     WHERE unpivoted.Id = TestTable.ID and Val = MaxVals.MaxVal) as MaxCol
FROM
    TestTable
    JOIN maxvals on maxvals.id = TestTable.Id