Search code examples
sqlsql-serverpivotdynamic-pivot

Dynamic Row Data into Column

I have a column which has 100 rows of data. I need to get the top 4 but in instead of rows I need to convert it into columns. Like Col1, Col2, Col3 and Col4.

I have tried 

SELECT 
    MAX (CASE 
            WHEN rss_name = 'BBC-Sports' 
               THEN rss_name 
         END) AS col1,
    MAX (CASE 
            WHEN rss_name = 'Talk Sports' 
               THEN rss_name 
         END) AS col2,
    MAX (CASE 
            WHEN rss_name = 'Sky Sports' 
               THEN rss_name 
         END) AS col3,
    MAX (CASE 
            WHEN rss_name = 'Crick Info' 
               THEN rss_name 
         END) AS col4 
FROM 
    RSS

but it only works with static values:

enter image description here

I need

 Col1,   Col2,       Col3,      Col4
 Sports,Talk Sports,Sky Sports,Crick Info

but since this is not constant data it will change and the values in Col keep changing.

Solution

  • You could use a derived table to set your column order then use your conditional aggregation on that.

    SELECT 
    MAX(CASE WHEN Col_Rn = 1 THEN Rss_Name END) AS Col1,
    MAX(CASE WHEN Col_Rn = 2 THEN Rss_Name END) AS Col2,
    MAX(CASE WHEN Col_Rn = 3 THEN Rss_Name END) AS Col3,
    MAX(CASE WHEN Col_Rn = 4 THEN Rss_Name END) AS Col4
FROM (
       SELECT    Rss_Name, 
                 Row_Number() OVER (ORDER BY Rss_Name) AS Col_Rn -- set your order here
       FROM      RSS
    ) t