Dynamic conversion of long data to wide with multiple columns

Question

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?

• Erik Reasonable Rates Darling: Why don't you try a PRINT to do some basic syntax troubleshooting?
• SQLserving: @sp_BlitzErik I've never used PRINT but I'm trying to look up how to use it to fix my code, could you give a lil more detail on what I should be looking for with respect to syntax troubleshooting?
• Erik Reasonable Rates Darling: No. Print each variable and validate that the output is what you expect and that the code can run.
• KumarHarsh: you are getting error because you can only do one pivot at a time.
• KumarHarsh: neither it is working for one column nor you hv shown your desire output.

Answer 1

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

Answer 2

if you already know all the possible values for Section + score type, you can try the pivot / unpivot trick like:

create table my_table(ID int, SchoolID int, Section int, RepScore decimal(8, 2), SportsScore decimal(8, 2), PartyScore decimal(8, 2))
insert into my_table
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) 
 (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 SchoolID, 
 [RepScore1],
 [SportsScore1],
 [PartyScore1],
 [RepScore6],
 [SportsScore6],
 [PartyScore6],
 [RepScore7],
 [SportsScore7],
 [PartyScore7],
 [RepScore10],
 [SportsScore10],
 [PartyScore10],
 [RepScore11],
 [SportsScore11],
 [PartyScore11],
 [RepScore12],
 [SportsScore12]
from (
 select SchoolID, score, col+cast(Section as varchar) as col
 from my_table t
 unpivot(score for col in (RepScore, SportsScore, PartyScore)) up
 ) t
pivot(max(score) for col in([RepScore1],
 [SportsScore1],
 [PartyScore1],
 [RepScore6],
 [SportsScore6],
 [PartyScore6],
 [RepScore7],
 [SportsScore7],
 [PartyScore7],
 [RepScore10],
 [SportsScore10],
 [PartyScore10],
 [RepScore11],
 [SportsScore11],
 [PartyScore11],
 [RepScore12],
 [SportsScore12])) p

Answer 3

This is how I ended up doing it.

DECLARE @cols VARCHAR(2000) 
DECLARE @sqlquery2 VARCHAR(2000) 
DECLARE @sqlquery3 VARCHAR(2000) 
DECLARE @sqlquery4 VARCHAR(2000) 
DECLARE @sqlquery5 VARCHAR(2000) 
DECLARE @sqlquery VARCHAR(2000) 
SELECT @cols = STUFF(( SELECT distinct '], [' +cast([Section] as varchar)
 FROM dbo.SchoolData FOR XML PATH('') ), 1, 2, '') + ']' 
SET @sqlquery = 'SELECT * into ##a FROM 
 (SELECT ID=1,z.Section,z.IRI1 
 FROM dbo.SchoolData z) base 
 PIVOT (Max(IRI1) FOR [Section] IN (' + @cols + ')) AS finalpivot' 
SET @sqlquery2 = 'SELECT * into ##b FROM 
 (SELECT ID=1,z.Section,z.IRI2 
 FROM dbo.SchoolData z) base 
 PIVOT (Max(IRI2) FOR [Section] IN (' + @cols + ')) AS finalpivot' 
SET @sqlquery3 = 'SELECT * into ##c FROM 
 (SELECT ID=1,z.Section,z.RUT 
 FROM dbo.SchoolData z) base 
 PIVOT (Max(RUT) FOR [Section] IN (' + @cols + ')) AS finalpivot' 
SET @sqlquery4 = 'SELECT * into ##d FROM 
 (SELECT ID=1,z.Section,z.RN1 
 FROM dbo.SchoolData z) base 
 PIVOT (Max(RN1) FOR [Section] IN (' + @cols + ')) AS finalpivot' 
SET @sqlquery5 = 'SELECT * into ##e FROM 
 (SELECT ID=1,z.Section,z.RN2 
 FROM dbo.SchoolData z) base 
 PIVOT (Max(RN2) FOR [Section] IN (' + @cols + ')) AS finalpivot' 
Execute(@sqlQuery) 
Execute(@sqlQuery2) 
Execute(@sqlQuery3) 
Execute(@sqlQuery4) 
Execute(@sqlQuery5) 
Select * from ##a a join ##b b on a.ID=b.ID join ##c c on a.ID=c.ID join ##d d on a.ID=d.ID join ##e e on a.ID=e.ID 
drop table ##a 
drop table ##b 
drop table ##c 
drop table ##d 
drop table ##e