Expand multiple columns in 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 to select and make data displayed as

with Eng1, Eng2, and Eng3 being column headers

This is what I got so far, but this is 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 am 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) 

Answer 2

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

To get the result, you will need to first parse the name , icon and totalcount , then you can use the anchor point to create new columns. You can use the UNPIVOT or CROSS APPLY function to convert columns to rows. The request to convert data from columns to rows:

 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 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 the 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 correct logic, you can convert it 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 produce the result:

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