CODEWITHSUNDEEP
sql-serverunpivot
Carl
Carl

Reputation: 1369

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                   | 
+--------+------+------+------+------+------+

Upvotes: 1

Views: 239

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93724

Here is one trick to do it using Cross Apply and Table Valued Constructor 

SELECT Id,
       maxCols= Stuff(cs.maxCols, 1, 1, '')
FROM   Yourtable
       CROSS apply(SELECT(SELECT TOP 3 ',' + NAME
                          FROM   (VALUES (colA,'colA'),(colB,'colB'),(colC,'colC'),
                                         (colD,'colD'),(colE,'colE')) tc (val, NAME)
                          ORDER  BY val DESC
                          FOR xml path, type).value('.[1]', 'nvarchar(max)')) cs (maxCols)

If needed it can be made dynamic using Information_schema.Columns

Upvotes: 1

TriV
TriV

Reputation: 5148

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

Upvotes: 2

Related Questions