SQL Server : column name of max of several columns for each row

Question

I have a table called campaigns that shows the number of 'tiles' each category is displayed in in an email campaign, like below

    campaign_id     categoryA_tiles   categoryB_tiles   categoryC_tiles ... cateogory Z_tiles
        1               5                7                    4         ...      4
        2               4                4                    9         ...      1

I need to add 3 columns to this table that show the 3 most frequently occurring categories (i.e. the column names) for each email campaign. For example I would like the result to look like this

campaign_id ...  category_1    category_2    category_3
    1       ...   categoryB    categoryA     categoryZ
    2       ...   categoryC    categoryA     categoryB

Note that there may be ties as in the second row, in which case I'd like them to be displayed from left to right.

This is essentially the same as this question but I'm using SSMS 2012 rather than MySQl. This answer is what I'm after but it is incompatible with SSMS. To be specific, the error I can't fix is how @rownum is defined and used.

Can anyone help?

Answer 1

The syntax between SQL Server and others is not the same (especially with row number - see the docs for ROW_NUMBER).

Try this:

DECLARE @campaigns TABLE (campaign_id int, categoryA_tiles int, categoryB_tiles int,  categoryC_tiles int, categoryZ_tiles int)

INSERT INTO @campaigns VALUES
(1,5,7,4,4),
(2,4,4,9,1),
(3,2,5,9,10),
(4,1,8,9,11)

SELECT
  t2.campaign_id,
  max(case when t2.RowNumber=1 then t2.name end) as 'highest value',
  max(case when t2.RowNumber=2 then t2.name end) as '2nd highest value',
  max(case when t2.RowNumber=3 then t2.name end) as '3rd highest value',
  max(case when t2.RowNumber=4 then t2.name end) as '4th highest value'
FROM 
    (SELECT campaign_id, name, amt,  ROW_NUMBER() OVER (PARTITION BY campaign_id ORDER BY amt DESC) AS RowNumber 
      FROM 
        (SELECT campaign_id, categoryA_tiles AS amt, 'categoryA_tiles' AS name FROM @campaigns UNION 
         SELECT campaign_id, categoryB_tiles AS amt, 'categoryB_tiles' AS name FROM @campaigns UNION
         SELECT campaign_id, categoryC_tiles AS amt, 'categoryC_tiles' AS name FROM @campaigns UNION
         SELECT campaign_id, categoryZ_tiles AS amt, 'categoryZ_tiles' AS name FROM @campaigns) t1) t2 
GROUP BY t2.campaign_id

The answer will be:

campaign_id    highest value       2nd highest value    3rd highest value   4th highest value
    1          categoryB_tiles     categoryA_tiles      categoryC_tiles     categoryZ_tiles
    2          categoryC_tiles     categoryA_tiles      categoryB_tiles     categoryZ_tiles
    3          categoryZ_tiles     categoryC_tiles      categoryB_tiles     categoryA_tiles
    4          categoryZ_tiles     categoryC_tiles      categoryB_tiles     categoryA_tiles

Answer 2

Hi you can solve this using UNPIVOT, please try the below script

DECLARE @campaigns TABLE (campaign_id int, categoryA_tiles int, categoryB_tiles int,  categoryC_tiles int, categoryZ_tiles int)

INSERT INTO @campaigns VALUES (1,5,7,4,4),(2,4,4,9,1)

SELECT T.campaign_id,
        MAX((CASE WHEN RowNumber=1 THEN [tVal] END)) AS Category1,
        MAX((CASE WHEN RowNumber=2 THEN [tVal] END)) AS Category2,
        MAX((CASE WHEN RowNumber=3 THEN [tVal] END)) AS Category3
FROM    (
    SELECT campaign_id,tVal,MaxVal,ROW_NUMBER() OVER (PARTITION BY campaign_id ORDER BY MaxVal DESC) AS RowNumber
    FROM @campaigns t1
    UNPIVOT ( MaxVal FOR tVal IN ( categoryA_tiles, categoryB_tiles, categoryC_tiles ,categoryZ_tiles) ) AS u
)   T
GROUP BY T.campaign_id

Here you can add/remove more column/category as per your requirement (or can join with tables - campaigns or other tables - for additional columns).

Result:

campaign_id Category1       Category2       Category3
1           categoryB_tiles categoryA_tiles categoryC_tiles
2           categoryC_tiles categoryA_tiles categoryB_tiles