Início / dba / Perguntas / 192532
Accepted
SQLserving
Asked: 2017-12-07 06:37:13 +0800 CST

Conversão dinâmica de dados longos para amplos com várias colunas

  • 772

Estou tentando converter dados longos: 

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

para dados amplos (estou usando apenas as três primeiras linhas para mantê-lo curto aqui): 

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

Eu tentei isso: 

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 )

Este é o erro que recebo: Msg 8114, Level 16, State 1, Line 7 Erro ao converter o tipo de dados nvarchar para int. Msg 473, Level 16, State 1, Line 7 O valor incorreto "a9" é fornecido no operador PIVOT. Msg 207, Level 16, State 1, Line 8 Nome de coluna inválido 'Section'.

Quando eu tento com apenas um assim: 

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'

Ele funciona para apenas uma coluna e fornece nomes de coluna como este [1],[6],[7]. Mas não funciona para várias colunas como eu preciso. Alguma ideia?

sql-server xml

3 respostas

  1. você está procurando por isso,

    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

  2. se você já conhece todos os valores possíveis para Seção + tipo de pontuação, você pode tentar o truque de pivô / unpivot como:

    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
    • 1
  3. Best Answer

    Foi assim que acabei fazendo.

    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
    • 0

relate perguntas