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)
Advertisement
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.