0

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?

5
  • 1
    Why don't you try a PRINT to do some basic syntax troubleshooting? 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. 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

3 Answers 3

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
1

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
0

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  

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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