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

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!

Answer 1

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)