SQL：确定哪一列在每一行中具有最大值

Question

我有一个列ID为A，B，C，D，E ...的表（共10个数字列）

对于每一行，I需要找到哪个列具有最大值，以及该值是多少。

E.G.这里是1排我的表：
ID A B C D E F G H I J
XY 5 4 9 5 0 1 3 2 1 7

我要生成2个新列：
MAXVALUE，这将等于9，和 MAXCOL，这将等于 “C”

任何意见，除了大量的IF声明？

Answer 1

我没有一个SQL处理器手的事，但沿着使用unpivot假设你的表格中的解决方案被命名为“TestTable的”中的

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) 

Answer 2

线：

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