Dynamic Row Data into Column

Question

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:

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.

Is there a particular order that this initial list of rows should be placed in so that we may grab "the top 4", or are you happy with using whatever order they happen to be in in the table? — toonice, Apr 11 '17 at 09:29
Have you tried reading this: http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server — ttwis, Apr 11 '17 at 09:30

score 2 · Accepted Answer · answered Apr 11 '17 at 14:38

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

score 0 · Answer 2 · answered Apr 11 '17 at 10:26

You need to use Dynamic Pivot. But in your case besides you need an extra column for Column names in Pivot like COL_1, COL_2....

Schema: (From your Image. Its better if you provide this sample data in Text).

CREATE TABLE #TAB (Rss_Name VARCHAR(50))

INSERT INTO #TAB
SELECT 'Sports'
UNION ALL
SELECT 'Talk Sports'
UNION ALL
SELECT 'Sky Sports'
UNION ALL
SELECT 'Crick Info'

Now Prepare your dynamic query as below

DECLARE @SQL VARCHAR(MAX)='',@PVT_COL VARCHAR(MAX)='';

--Preparing Dynamic Column List 
SELECT @PVT_COL =@PVT_COL
+ '[COL_'+CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS VARCHAR(4))+'],'
FROM #TAB
SELECT @PVT_COL = LEFT(@PVT_COL,LEN(@PVT_COL)-1)


SELECT @SQL = 
'SELECT * FROM (
SELECT Rss_Name
,''COL_''+CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS VARCHAR(4)) AS COL_NME 
FROM #TAB
)AS A
PIVOT
(
    MAX(Rss_Name) FOR COL_NME IN ('+@PVT_COL+')
)PVT'

EXEC (@SQL)

Result:

+--------+-------------+------------+------------+
| COL_1  |    COL_2    |   COL_3    |   COL_4    |
+--------+-------------+------------+------------+
| Sports | Talk Sports | Sky Sports | Crick Info |
+--------+-------------+------------+------------+

Dynamic Row Data into Column

2 Answers2