SQL Server 使用 PIVOT 和 UNPIVOT 實(shí)現(xiàn)多條行記錄轉(zhuǎn)置為列表
當(dāng)前位置：點(diǎn)晴教程→知識管理交流
→『 技術(shù)文檔交流 』
可以使用
語法以下語法總結(jié)了如何使用 select <non-pivoted column>, [first pivoted column] AS <column name>, [second pivoted column] AS <column name>, ... [last pivoted column] AS <column name> from (<select query that produces the data>) AS <alias for the source query> PIVOT ( <aggregation function>(<column being aggregated>) FOR [<column that contains the values that will become column headers>] IN ( [first pivoted column], [second pivoted column], ... [last pivoted column]) ) AS <alias for the pivot table> <optional ORDER BY clause>; select <非透視的列>, [第一個(gè)透視的列] AS <列名稱>, [第二個(gè)透視的列] AS <列名稱>, ... [最后一個(gè)透視的列] AS <列名稱>, from (<生成數(shù)據(jù)的 select 查詢>) AS <源查詢的別名> PIVOT ( <聚合函數(shù)>(<要聚合的列>) FOR [<包含要成為列標(biāo)題的值的列>] IN ( [第一個(gè)透視的列], [第二個(gè)透視的列], ... [最后一個(gè)透視的列]) ) AS <透視表的別名> <可選的 ORDER BY 子句>; 備注
簡單 PIVOT 示例下面的代碼示例生成一個(gè)兩列四行的表： USE AdventureWorks2014 ; GO select DaysToManufacture, AVG(StandardCost) AS AverageCost from Production.Product GROUP BY DaysToManufacture; 下面是結(jié)果集： DaysToManufacture AverageCost ----------------- ----------- 0 5.0885 1 223.88 2 359.1082 4 949.4105 沒有定義 以下代碼顯示相同的結(jié)果，該結(jié)果經(jīng)過透視以使 -- Pivot table with one row and five columns select 'AverageCost' AS Cost_Sorted_By_Production_Days, [0], [1], [2], [3], [4] from ( select DaysToManufacture, StandardCost from Production.Product ) AS SourceTable PIVOT ( AVG(StandardCost) FOR DaysToManufacture IN ([0], [1], [2], [3], [4]) ) AS PivotTable; 下面是結(jié)果集： Cost_Sorted_By_Production_Days 0 1 2 3 4 ------------------------------ ----------- ----------- ----------- ----------- ----------- AverageCost 5.0885 223.88 359.1082 NULL 949.4105 復(fù)雜 PIVOT 示例若要生成交叉表報(bào)表來匯總數(shù)據(jù)，通?？赡軙l(fā)現(xiàn) USE AdventureWorks2014; GO select VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5 from (select PurchaseOrderID, EmployeeID, VendorID from Purchasing.PurchaseOrderHeader) p PIVOT ( COUNT (PurchaseOrderID) FOR EmployeeID IN ( [250], [251], [256], [257], [260] ) ) AS pvt ORDER BY pvt.VendorID; 以下為部分結(jié)果集： VendorID Emp1 Emp2 Emp3 Emp4 Emp5 ----------- ----------- ----------- ----------- ----------- ----------- 1492 2 5 4 4 4 1494 2 5 4 5 4 1496 2 4 4 5 5 1498 2 5 4 4 4 1500 3 4 4 5 4 將在 select PurchaseOrderID, EmployeeID, VendorID from PurchaseOrderHeader;
逆透視示例與 -- create the table and insert values as portrayed in the previous example. create TABLE pvt (VendorID INT, Emp1 INT, Emp2 INT, Emp3 INT, Emp4 INT, Emp5 INT); GO insert INTO pvt VALUES (1,4,3,5,4,4); insert INTO pvt VALUES (2,4,1,5,5,5); insert INTO pvt VALUES (3,4,3,5,4,4); insert INTO pvt VALUES (4,4,2,5,5,4); insert INTO pvt VALUES (5,5,1,5,5,5); GO -- Unpivot the table. select VendorID, Employee, Orders from (select VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 from pvt) p UNPIVOT (Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5) )AS unpvt; GO 以下為部分結(jié)果集： VendorID Employee Orders ----------- ----------- ------ 1 Emp1 4 1 Emp2 3 1 Emp3 5 1 Emp4 4 1 Emp5 4 2 Emp1 4 2 Emp2 1 2 Emp3 5 2 Emp4 5 2 Emp5 5 ... 請注意，
另請參閱from (Transact-SQL) 該文章在 2023/6/28 9:19:10 編輯過 |
關(guān)鍵字查詢
相關(guān)文章
正在查詢... 
|
400 186 1886
