Q:

Getting the column name that holds the maximum value of a row

I have generated an extensive view which simulates certain occurrences based on different statistic models. These models are defined in each column header by a number and the maximum value of a row is the best model. 
The table generated looks (partially) as follows;
+--------+----+------+------+------+------+------+------+
| 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.

Now I want to find the highest value in each row and display the corresponding column name as such;
| 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.

This is derived from 100254 - 2 - M1 largest value 46 occurring in column 0506, etc.
I've been toying around with PIVOT functions but no success there. I've also looked for an Index/Match equivalent like in Excel but since I can't refer to column headers as values this obviously won't work (and haven't found such function either).
Any help would be hugely appreciated.

UPDATE per Damien's comment:
An excerpt from the code that led to this:
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

No doubt that this may be one of the most ineffective uses of SQL, but time-pressure and inexperience made me write it this way. 
Any suggestions to change this code and achieve the desired table result more efficiently would be much appreciated too.

EDIT per Anton's answer;
The above code summarized as pvt continues as follows;
SELECT Number, LI, PHSE, combo, hitrate

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

Great solution to an inelegant problem. 

A:

You need to use UNPIVOT, not PIVOT
https://msdn.microsoft.com/en-us/library/ms177410.aspx
If the number of columns is variable, you have to use dynamic sql to construct the list of columns

