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?
PRINT
to do some basic syntax troubleshooting?