1

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
Improve this question

2 Answers 2

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.