Search code examples
sql-serverunpivot

Return column names based on which holds the maximum value in the record

I have a table with the following structure ...

+--------+------+------+------+------+------+
| ID     | colA | colB | colC | colD | colE | [...] etc.
+--------+------+------+------+------+------+
| 100100 | 15   | 100  | 90   | 80   | 10   | 
+--------+------+------+------+------+------+
| 100200 | 10   | 80   | 90   | 100  | 10   | 
+--------+------+------+------+------+------+
| 100300 | 100  | 90   | 10   | 10   | 80   | 
+--------+------+------+------+------+------+

I need to return a concatenated value of column names which hold the maximum 3 values per row ...

+--------+----------------------------------+
| ID     | maxCols                          |
+--------+----------------------------------+
| 100100 | colB,colC,colD                   |
+--------+------+------+------+------+------+
| 100200 | colD,colC,colB                   | 
+--------+------+------+------+------+------+
| 100300 | colA,colB,colE                   | 
+--------+------+------+------+------+------+
  • It's okay to not concatenate the column names, and have maxCol1 | maxCol2 | maxCol3 if that's simpler
  • The order of the columns is important when concatenating them
  • The number of columns is limited and not dynamic
  • The number of rows is many
Solution

  • You could use UNPIVOT and get TOP 3 for each ID

    ;with temp AS
(
    SELECT ID, ColValue, ColName
    FROM @SampleData sd
    UNPIVOT
    (
       ColValue For ColName in ([colA], [colB], [colC], [colD], [colE])
    ) unp
)
SELECT sd.ID, ca.ColMax
FROM @SampleData sd
CROSS APPLY
(
    SELECT STUFF(
              (
                SELECT TOP 3 WITH TIES
                       ',' + t.ColName
                FROM temp t
                WHERE t.ID = sd.ID
                ORDER BY t.ColValue DESC
                FOR XML PATH('')
              )
             ,1,1,'') AS ColMax
) ca

    See demo here: http://rextester.com/CZCPU51785