Pivot multiple columns into rows in SQL Server?

Question

I have a table with data like this

create table temp
(
    colName varchar(50),
    name varchar(50),
    icon varchar(150),
    totalcount int
)

insert into temp values ('Eng1', 'Following', 'followingicon.png', 1564)

insert into temp values ('Eng2','Total Followers', 'followericon.png', 500)

insert into temp values ('Eng3','Direct Messages', 'messageicon.png', 800)

How do I select and make the data appear as

End Result

with Eng1, Eng2, and Eng3 being column headers

This is what I got so far but it does only the first level. I need to get all three levels

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.colName) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT ' + @cols + ' from 
            (
                select colName              
                    , totalcount
                from temp
           ) x
            pivot 
            (
                 max( totalcount)
                for colName in (' + @cols + ')               
            ) p'

execute(@query)

I'm using SQL server 2008.

Thank you for your help!

score 3 · Answer 1 · answered Sep 05 '13 at 19:39

Here is a way:

DECLARE @cols   AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.colName) 
            FROM #temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')


SET @query = 'SELECT ' + @cols + ' FROM 
            (
                SELECT y.*
                FROM #Temp t
                CROSS APPLY 
                (
                    VALUES (t.colname,CONVERT(VARCHAR(150),name),''name''),
                           (t.colname,icon,''icon''),
                           (t.colname,CONVERT(VARCHAR(150),totalcount),''totalcount'')
                ) y (colName, value, Data)
           ) x
            PIVOT 
            (
                 MAX(Value)
                FOR colName IN (' + @cols + ')               
            ) p'

EXECUTE(@query)

score 3 · Answer 2 · answered Sep 05 '13 at 19:45

My suggestion when you are working with dynamic SQL would be to write a hard-coded version of the query first to get the logic correct, then convert it to dynamic SQL.

To get the result, you will have to look at unpivoting the name, icon and totalcount columns first, then you can apply the pivot to create your new columns. You can use either the UNPIVOT function or CROSS APPLY to transform the columns into rows. The query to convert the data from columns into rows would be:

select colname, origCol, value
from temp
cross apply
(
  select 'name', name union all
  select 'icon', icon union all
  select 'totalcount', cast(totalcount as varchar(50))
) c (origCol, value)

See Demo. This gives you the data in the format:

| COLNAME |    ORIGCOL |             VALUE |
|    Eng1 |       name |         Following |
|    Eng1 |       icon | followingicon.png |
|    Eng1 | totalcount |              1564 |
|    Eng2 |       name |   Total Followers |

Then you can apply the PIVOT function to your new colname values:

select Eng1, Eng2, Eng3
from 
(
  select colname, origCol, value
  from temp
  cross apply
  (
    select 'name', name union all
    select 'icon', icon union all
    select 'totalcount', cast(totalcount as varchar(50))
  ) c (origCol, value)
) src
pivot
(
  max(value)
  for colname in (Eng1, Eng2, Eng3)
) piv

See SQL Fiddle with Demo. As I said, you need a dynamic version so now that you have the logic correct you can convert this to a dynamic SQL query:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.colName) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT ' + @cols + ' 
          from 
          (
            select colname, origCol, value
            from temp
            cross apply
            (
              select ''name'', name union all
              select ''icon'', icon union all
              select ''totalcount'', cast(totalcount as varchar(50))
            ) c (origCol, value)
           ) x
            pivot 
            (
                 max(value)
                for colName in (' + @cols + ')               
            ) p'

execute sp_executesql @query;

See SQL Fiddle with Demo. This will give a result:

|              ENG1 |             ENG2 |            ENG3 |
| followingicon.png | followericon.png | messageicon.png |
|         Following |  Total Followers | Direct Messages |
|              1564 |              500 |             800 |

Great explanation. I understand better my problem and your solution now. Thanks a lot! — Lac Ho, Sep 05 '13 at 19:54

Pivot multiple columns into rows in SQL Server?

2 Answers2