Return column name of max value in a row SQL Server

Initial Table:

    Name    Job1    Job2     Job3     Job4     Job5     Job6
--------------------------------------------------------------
    A        34       0        66      0        0       0
    B        33       33       33      1        0       0
    C        100      0         0      0        0       0
    D        50       0         0      0        0       50

Output Table:

    Name    Max           Job1     Job2     Job3     Job4     Job5     Job6
----------------------------------------------------------------------------
    A       Job3            34       0        66      0        0       0
    B       Job1;Job2;Job3  33       33       33      1        0       0
    C       Job1            100      0         0      0        0       0
    D       Job1;Job6       50       0         0      0        0       50

I need to get the Job Name (column name) of the row with the max value in SQL Server. Is there a way other than else if to do this because ‘greatest’ function is not available in SQL? (Coz I have 50 Jobs at least)

Answer

You can use apply and window functions:

select t.*, max_cols
from t cross apply
     (select string_agg(colname, ';') as max_cols
      from (select top (1) with ties v.colname
            from (values ('Job1', t.job1), ('Job2', t.job2), . . . 
                 ) v(colname, job)
            order by v.job desc
           ) v
     ) v;

That said, you should probably fix your data model. Having multiple columns with the same information is usually a sign of a deficient data model. Each name/job combination should be a separate row in another table.