使用 PIVOT 和 UNPIVOT

基本 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 pvt.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] 資料庫 [檢視] 資料夾下的檢視。以滑鼠右鍵按一下檢視名稱，然後選取 [編寫檢視的指令碼為]。

請參閱

參考