使用 PIVOT 和 UNPIVOT
更新： 2006 年 7 月 17 日
您可以使用 PIVOT 和 UNPIVOT 關係運算子，將資料表值運算式變更為另一個資料表。PIVOT 會將運算式內一個資料行中的唯一值轉成輸出中的多個資料行，以旋轉資料表值運算式，然後依據最終輸出的需要，對其餘的任何資料行值執行必要的彙總。UNPIVOT 執行的作業則與 PIVOT 相反，它會將資料表值運算式旋轉為資料行值。
.gif "ms177410.note(zh-tw,SQL.90).gif") 附註： |
|---|
| 對升級為 Microsoft SQL Server 2005 的資料庫使用 PIVOT 和 UNPIVOT 時，資料庫的相容性層級必須設為 90。如需有關如何設定資料庫相容性層級的詳細資訊，請參閱＜sp_dbcmptlevel (Transact-SQL)＞。 |
PIVOT 提供的語法比您另外指定一連串複雜的 SELECT...CASE 陳述式，還要簡單易讀。如需 PIVOT 語法的完整描述，請參閱＜FROM (Transact-SQL)＞。
下列是註解的 PIVOT 語法。
SELECT <非樞紐資料行> ,
[第一個樞紐資料行] AS <資料行名稱> ,
[第二個樞紐資料行] AS <資料行名稱> ,
...
[最後一個樞紐資料行] AS <資料行名稱>
FROM
( <產生資料的 SELECT 查詢> )
AS <來源查詢的別名>
PIVOT
(
<aggregation function>( <要彙總的資料行> )
FOR
[<含有將成為資料行標頭值的資料行>]
IN``( [第一個樞紐資料行] , [第二個樞紐資料行] ,
... [最後一個樞紐資料行] )
)``AS <樞紐資料表的別名>
<選擇性 ORDER BY 子句>
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>
基本 PIVOT 範例
下列程式碼範例會產生包含兩個資料行的資料表，其中有四個資料列。
USE AdventureWorks ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture
以下為結果集：
DaysToManufacture AverageCost
0 5.0885
1 223.88
2 359.1082
4 949.4105
沒有任何產品是定義為三天內完工 (DaysToManufacture)。
下列程式碼會顯示同樣的結果，但是經過樞紐處理後，讓 DaysToManufacture 值變成了資料行的標題。即使結果為 NULL，還是為這三 [3] 天產生了一個資料行。
-- 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
以下為結果集：
Cost_Sorted_By_Production_Days 0 1 2 3 4
AverageCost 5.0885 223.88 359.1082 NULL 949.4105
複雜 PIVOT 範例
當您想要產生跨表格式報表來建立資料摘要時，這個常見的狀況可以顯出 PIVOT 的用處。例如，假設您想要查詢 AdventureWorks 範例資料庫中的 PurchaseOrderHeader 資料表，以判斷某些員工所下的訂單數目。下列查詢會提供這個報表，並依供應商排序：
USE AdventureWorks;
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID
以下為部分結果集。
VendorID Emp1 Emp2 Emp3 Emp4 Emp5
1 4 3 5 4 4
2 4 1 5 5 5
3 4 3 5 4 4
4 4 2 5 5 4
5 5 1 5 5 5
這個子選擇陳述式所傳回的結果，是根據 EmployeeID 資料行進行樞紐處理而來。
SELECT PurchaseOrderID, EmployeeID, VendorID
FROM PurchaseOrderHeader
這表示由 EmployeeID 資料行所傳回的唯一值本身會變成最終結果集中的欄位。因此，樞紐子句指定的每個 EmployeeID 編號都會有一個資料行：在此情況下，是以下員工 164、198、223、231 和 233。PurchaseOrderID 資料行會當作數值資料行，這是在最終輸出中傳回的資料行 (稱為群組資料行) 所根據以進行分組的資料行。在此情況下，COUNT 函數會對群組資料行進行彙總。請注意，此時會顯示警告訊息，指出計算每個員工的 COUNT 時，並未考慮 PurchaseOrderID 資料行中出現的任何 NULL 值。
| 重要事項： |
|---|
| 使用彙總函數搭配 PIVOT，在計算彙總時不會考慮值資料行中出現的任何 NULL 值。 |
UNPIVOT 執行的作業則幾乎與 PIVOT 完全相反，它會將資料行旋轉成資料列。假設上述範例中所產生的資料表在資料庫中是儲存為 pvt，而現在您想要將資料行識別碼 Emp1、Emp2、Emp3、Emp4 和 Emp5 旋轉成對應到特定供應商的資料列值。這表示您必須識別兩個額外的資料行。包含所要旋轉的資料行值 (Emp1、Emp2、...) 的資料行將命名為 Employee，而保留目前位在所要旋轉資料行之下的值的資料行則命名為 Orders。在 Transact-SQL 定義中，這些資料行會分別對應到 pivot_column 和 value_column。查詢內容如下。
--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
以下為部分結果集。
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
...
請注意，UNPIVOT 並非與 PIVOT 完全相反。PIVOT 會執行彙總，因此它會將多個可能的資料列合併成輸出中的單一資料列。因為資料列已經合併，所以 UNPIVOT 所產生的並不是原始的資料表值運算式結果。此外，UNPIVOT 輸入中的 NULL 值會在輸出中消失不見，但是在執行 PIVOT 作業之前，輸入中原本可能有 NULL 值。
AdventureWorks 範例資料庫中的 Sales.vSalesPersonSalesByFiscalYears 檢視使用了 PIVOT 來傳回每位銷售人員在每個會計年度的總銷售額。若要在 SQL Server Management Studio 中編寫檢視的指令碼，請在 [物件總管] 中找出 [AdventureWorks] 資料庫 [檢視] 資料夾下的檢視。以滑鼠右鍵按一下檢視名稱，然後選取 [編寫檢視的指令碼為]。
請參閱
其他資源
FROM (Transact-SQL)
CASE (Transact-SQL)
說明及資訊
變更歷程記錄
| 版本 | 歷程記錄 |
|---|---|
2006 年 7 月 17 日 |
|