Sql-server – Dynamic conversion of long data to wide with multiple columns

cursorsdynamic-sqlpivotsql serverxml

I'm trying to convert long data:

ID|SchoolID|Section|RepScore|SportsScore|PartyScore  
1 |20      |1      |23.2    |70.2       |42.3          
2 |20      |6      |65.2    |75.8       |52.3        
3 |20      |7      |77.2    |72.2       |66.3       
4 |21      |10     |13.2    |40.2       |72.3         
5 |21      |11     |25.2    |55.8       |72.3         
6 |21      |12     |37.2    |62.2       |76.3       

to wide data (I am using only the first three rows to keep it short here):

SchoolID|RpScr1|RpScr6|RpScr7|SprtScr1|SprtScr6|SprtScr7|Prty1|Prty6|Prty7  
20      |23.2  |65.2  |77.2  |70.2    |75.8    |72.2    |42.3 |52.3 |66.3 

I have tried this:

DECLARE @cols VARCHAR(1000)
DECLARE @cols2 VARCHAR(1000)
DECLARE @cols3 VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)
SELECT  @cols = STUFF(( SELECT distinct  '], [a' + cast([Section] as varchar)
                        FROM [dbo].[SchoolData] FOR XML PATH('') ), 1, 2, '') + ']'
SELECT  @cols2 = STUFF(( SELECT distinct  '], [b' + cast([Section] as varchar)
                        FROM [dbo].[SchoolData] FOR XML PATH('') ), 1, 2, '') + ']'
SELECT  @cols3 = STUFF(( SELECT distinct  '], [c' + cast([Section] as varchar)
                        FROM [dbo].[SchoolData] FOR XML PATH('') ), 1, 2, '') + ']'
SET @sqlquery = 'SELECT * FROM
      (SELECT z.Section,z.RepScore,z.SportsScore,z.PartyScore
       FROM [dbo].[SchoolData] z) base
       PIVOT (Max(RepScore) FOR [Section] IN (' + @cols + ')) AS finalpivot
       PIVOT (Max(SportsScore) FOR [Section] IN (' + @cols + ')) AS finalpivot2
       PIVOT (Max(PartyScore) FOR [Section] IN (' + @cols + ')) AS finalpivot3'


EXECUTE ( @sqlquery )

This is the error I get: Msg 8114, Level 16, State 1, Line 7 Error
converting data type nvarchar to int. Msg 473, Level 16, State 1, Line
7 The incorrect value "a9" is supplied in the PIVOT operator. Msg 207,
Level 16, State 1, Line 8 Invalid column name 'Section'.

When I try it with just one like this:

DECLARE @cols VARCHAR(1000)  
DECLARE @sqlquery VARCHAR(2000)  
    SELECT  @cols = STUFF(( SELECT distinct  '], [a' + cast([Section] as varchar)  
                            FROM [dbo].[SchoolData] FOR XML PATH('') ), 1, 2, '') + ']'    
SET @sqlquery = 'SELECT * FROM  
          (SELECT z.Section,z.RepScore,z.SportsScore,z.PartyScore  
           FROM [dbo].[SchoolData] z) base  
           PIVOT (Max(RepScore) FOR [Section] IN (' + @cols + ')) AS   finalpivot' 

It works for just one column and gives column names like this [1],[6],[7]. But it does not work for multiple columns like I need. Any ideas?

Best Answer

are you looking for this,

CREATE TABLE #T(ID INT,SchoolID INT,Section INT,RepScore DECIMAL(5,2)
,SportsScore DECIMAL(5,2),PartyScore DECIMAL(5,2))
INSERT INTO #T VALUES
(1,20,1 ,23.2,70.2,42.3)         
,(2,20,6 ,65.2,75.8,52.3)       
,(3,20,7 ,77.2,72.2,66.3)      
,(4,21,10,13.2,40.2,72.3)        
,(5,21,11,25.2,55.8,72.3)        
,(6,21,12,37.2,62.2,76.3) 
--SELECT * FROM #T

DECLARE @colRpName VARCHAR(1000)
DECLARE @colRpVal VARCHAR(1000)
DECLARE @colSportVal VARCHAR(1000)
DECLARE @colPartyVal VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)

SELECT  
@colRpName = STUFF(( SELECT distinct  '], [' + cast([Section] as varchar)
                        FROM #T t1  FOR XML PATH('') ), 1, 2, '') + ']'
,  @colRpVal = STUFF(( SELECT distinct  ', [' + cast([Section] as varchar) +'] as '+'[a ' + cast([Section] as varchar)+']'
                        FROM #T t1  FOR XML PATH('') ), 1, 2, '') + ''
,  @colSportVal = STUFF(( SELECT distinct  ', [' + cast([Section] as varchar) +'] as '+'[b ' + cast([Section] as varchar)+']'
                        FROM #T t1  FOR XML PATH('') ), 1, 2, '') + ''
,  @colPartyVal = STUFF(( SELECT distinct  ', [' + cast([Section] as varchar) +'] as '+'[c ' + cast([Section] as varchar)+']'
                        FROM #T t1 FOR XML PATH('') ), 1, 2, '') + ''
from #T t

--SELECT @colRpName,@colRpVal

SET @sqlquery = '
SELECT fp.SchoolID,'+@colRpVal+'
,'+@colSportVal+'
,'+@colPartyVal+'
FROM  
(SELECT SchoolID,[section],RepScore

FROM #T z) base  
PIVOT (sum(RepScore) FOR [section] IN ('+@colRpName+')) AS   fp
CROSS APPLY 
(
SELECT fp1.SchoolID,'+@colSportVal+'
FROM  
(SELECT SchoolID,[section],SportsScore

FROM #T z) base1  
PIVOT (sum(SportsScore) FOR [section] IN ('+@colRpName+')) AS   fp1
WHERE fp1.SchoolID=fp.SchoolID
)ca
CROSS APPLY 
(
SELECT fp2.SchoolID,'+@colPartyVal+'
FROM  
(SELECT SchoolID,[section],PartyScore

FROM #T z) base2
PIVOT (sum(PartyScore) FOR [section] IN ('+@colRpName+')) AS   fp2
WHERE fp2.SchoolID=fp.SchoolID
)ca1
  ' 

--print @sqlquery
  EXEC(@sqlquery)-- please use sp_executesql

DROP TABLE #T