Sql-Server
長數據動態轉換為多列寬數據
我正在嘗試轉換長數據：
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到寬數據（我只使用前三行來保持簡短）：
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我試過這個：
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 )這是我得到的錯誤：Msg 8114, Level 16, State 1, Line 7 將數據類型 nvarchar 轉換為 int 時出錯。消息 473，級別 16，狀態 1，第 7 行 PIVOT 運算符中提供了不正確的值“a9”。消息 207，級別 16，狀態 1，第 8 行 列名“部分”無效。
當我嘗試這樣的一個時：
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'它只適用於一列並給出這樣的列名
$$ 1 $$,$$ 6 $$,$$ 7 $$. 但它不適用於我需要的多個列。有任何想法嗎？
你在找這個嗎
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
如果您已經知道 Section + score 類型的所有可能值，您可以嘗試以下樞軸/反樞軸技巧：
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