使用 PIVOT 和 UNPIVOT

您可以使用 PIVOT 和 UNPIVOT 關係運算子,將資料表值運算式變更為另一個資料表。PIVOT 會將運算式內一個資料行中的唯一值轉成輸出中的多個資料行,以旋轉資料表值運算式,然後依據最終輸出的需要,對其餘的任何資料行值執行必要的彙總。UNPIVOT 執行的作業則與 PIVOT 相反,它會將資料表值運算式旋轉為資料行值。

[!附註]

針對升級至 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

(

    <彙總函式>(<要彙總的資料行>)

FOR

[<包含將變成資料行標頭之值的資料行>]

    IN ( [第一個樞紐資料行], [第二個樞紐資料行],

    ... [最後一個樞紐資料行])

) AS <樞紐分析表的別名>

<選擇性的 ORDER BY 子句>;

基本 PIVOT 範例

下列程式碼範例會產生內含四個資料列的兩個資料行資料表。

USE AdventureWorks2008R2 ;
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 的用處。例如,假設您想要查詢 AdventureWorks2008R2 範例資料庫中的 PurchaseOrderHeader 資料表,以判斷某些員工所下的訂單數目。下列查詢會提供這個報表,並依供應商排序:

USE AdventureWorks2008R2;
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;

以下為部分結果集。

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

這個子選擇陳述式所傳回的結果,是根據 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 值。

AdventureWorks2008R2 範例資料庫中的 Sales.vSalesPersonSalesByFiscalYears 檢視會使用 PIVOT 來傳回每位銷售人員在每個會計年度的總銷售額。若要在 SQL Server Management Studio 中編寫檢視的指令碼,請在 [物件總管] 中找出 AdventureWorks2008R2 資料庫的 [檢視] 資料夾下的檢視。以滑鼠右鍵按一下檢視名稱,然後選取 [編寫檢視的指令碼為]