获取保存行最大值的列名

我已经生成了一个广泛的视图，该视图根据不同的统计模型来模拟某些事件。这些模型在每个列标题中均用数字定义，并且行的最大值是最佳模型。

生成的表看起来（部分）如下：

+--------+----+------+------+------+------+------+------+
| Number | LI | PHSE | 0505 | 0506 | 0507 | 0508 | 0509 | [...] etc.
+--------+----+------+------+------+------+------+------+
| 100254 | 2  | M1   |   44 |   46 |   45 |   44 |   44 |
| 100254 | 2  | M2   |   36 |   36 |   35 |   37 |   37 |
| 100254 | 2  | M3   |    5 |    5 |    5 |    5 |    5 |
| 100254 | 2  | R1   |   34 |   36 |   37 |   37 |   37 |
| 100254 | 2  | R2   |   41 |   41 |   40 |   41 |   41 |
| 100329 | 1  | M1   |   37 |   38 |   38 |   38 |   39 |
| 100329 | 1  | M2   |   31 |   29 |   28 |   29 |   29 |
| 100329 | 1  | M3   |    6 |    6 |    6 |    6 |    6 |
| 100329 | 1  | R1   |   29 |   29 |   29 |   30 |   30 |
| 100329 | 1  | R2   |   25 |   26 |   26 |   27 |   26 |
+--------+----+------+------+------+------+------+------+
  [...] etc.

现在，我想在每一行中找到最大值，并显示相应的列名；

| Number | LI | PHSE | MAXCOL |
+--------+----+------+--------+
| 100254 | 2  | M1   |   0506 |
| 100254 | 2  | M2   |   0508 |
| 100254 | 2  | M3   |   0505 |
| 100254 | 2  | R1   |   0507 |
| 100254 | 2  | R2   |   0505 |
+--------+----+------+--------+
  [...] etc.

这是从出现在0506列中的100254-2-M1最大值46等得出的。

我一直在玩弄PIVOT功能，但那里没有成功。我也一直在寻找与Excel中类似的索引/匹配项，但是由于我无法将列标题引用为值，因此这显然行不通（并且也没有找到这种功能）。

任何帮助将不胜感激。

根据Damien的评论进行更新：

摘录自以下代码：

SELECT DISTINCT sub2.Number, sub2.LI, sub2.PHSE
, sum(sub2.[0505]) over (partition by sub2.Number, sub2.LI, sub2.PHSE) as '0505'
, sum(sub2.[0506]) over (partition by sub2.Number, sub2.LI, sub2.PHSE) as '0506'
[...] etc. /*64 rows*/

FROM
    (SELECT DISTINCT sub.*
    , CASE WHEN sub.MF > sub.[5PAV] - sub.[5PSTDEV] THEN 1 ELSE 0 END AS '0505'
    , CASE WHEN sub.MF > sub.[5PAV] - sub.[6PSTDEV] THEN 1 ELSE 0 END AS '0506'
    [...] etc. /*64 rows*/

    FROM
        (SELECT DISTINCT ra.*
        , sum(ra.qtyr) OVER (partition BY ra.Number, ra.LI, ra.PHSE ORDER BY (ra.Number) rows BETWEEN 5 preceding AND 1 preceding) / 
        sum(ra.qtyu) OVER (partition BY ra.Number, ra.LI, ra.PHSE ORDER BY (ra.Number) rows BETWEEN 5 preceding AND 1 preceding) AS '5PAV'
        , sum(ra.qtyr) OVER (partition BY ra.Number, ra.LI, ra.PHSE ORDER BY (ra.Number) rows BETWEEN 6 preceding AND 1 preceding) / 
        sum(ra.qtyu) OVER (partition BY ra.Number, ra.LI, ra.PHSE ORDER BY (ra.Number) rows BETWEEN 6 preceding AND 1 preceding) AS '6PAV'
        [...] etc. /*8 rows*/

        , stdev(ra.MF) OVER (partition BY ra.Number, ra.LI, ra.PHSE ORDER BY (ra.Number) rows BETWEEN 4 preceding AND CURRENT row) AS '5PSTDEV'
        , stdev(ra.MF) OVER (partition BY ra.Number, ra.LI, ra.PHSE ORDER BY (ra.Number) rows BETWEEN 5 preceding AND CURRENT row) AS '6PSTDEV'
        [...] etc. /*8 rows*/

        FROM ra
        ) AS sub
    ) AS sub2

毫无疑问，这可能是SQL最无效的用法之一，但是时间压力和经验不足使我以这种方式编写它。

任何更改此代码并更有效地实现所需表结果的建议也将不胜感激。

根据安东的答案进行编辑；

上面的代码总结pvt如下。

SELECT Number, LI, PHSE, combo, hitrate

FROM (...) AS pvt
UNPIVOT 
(Hitrate FOR Combo IN (
                       [0505],
                       [0506],
                       [...] etc.)) AS upvt

很好的解决小问题的方法。