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?

Why don't you try a PRINT to do some basic syntax troubleshooting? — Erik Darling, Dec 6, 2017 at 14:38
@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? — SQLserving, Dec 6, 2017 at 15:37
No. Print each variable and validate that the output is what you expect and that the code can run. — Erik Darling, Dec 6, 2017 at 15:39
you are getting error because you can only do one pivot at a time. — KumarHarsh, Dec 8, 2017 at 8:27
neither it is working for one column nor you hv shown your desire output. — KumarHarsh, Dec 8, 2017 at 10:53

KumarHarsh · Accepted Answer · 2017-12-08 11:23:43Z

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

irimias · Accepted Answer · 2017-12-08 15:56:52Z

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

SQLserving · Accepted Answer · 2017-12-11 13:48:39Z

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

Stack Exchange Network

Dynamic conversion of long data to wide with multiple columns

3 Answers 3

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
xml
pivot
dynamic-sql
cursors
or ask your own question.

Hot Network Questions

Dynamic conversion of long data to wide with multiple columns

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serverxmlpivotdynamic-sqlcursors or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
xml
pivot
dynamic-sql
cursors
or ask your own question.