UNPIVOT 關鍵字將查詢的欄位一行顯示

使用 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 ;GOSELECT DaysToManufacture, AVG(StandardCost) AS AverageCost FROM Production.ProductGROUP BY DaysToManufacture;

下面是結果集：

DaysToManufacture          AverageCost

0                          5.0885

1                          223.88

2                          359.1082

4                          949.4105

沒有定義 DaysToManufacture 為 3 的產品。

以下代碼顯示相同的結果，該結果經過透視以使 DaysToManufacture 值成為欄位標題。提供一個列表示三
[3] 天，即使結果為 NULL。

-- Pivot table with one row and five columnsSELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, [0], [1], [2], [3], [4]FROM(SELECT DaysToManufacture, StandardCost     FROM Production.Product) AS SourceTablePIVOT(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;GOSELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5FROM (SELECT PurchaseOrderID, EmployeeID, VendorIDFROM Purchasing.PurchaseOrderHeader) pPIVOT(COUNT (PurchaseOrderID)FOR EmployeeID IN( [250], [251], [256], [257], [260] )) AS pvtORDER 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 語句返回的結果。

SELECT PurchaseOrderID, EmployeeID, VendorIDFROM PurchaseOrderHeader;

這意味著 EmployeeID 列返回的唯一值自行變成了最終結果集中的欄位。因此，在透視子句中指定的每個
Employee識別碼都有相應的一列：在本例中為僱員 164、198、223、231 和
233。PurchaseOrderID 列作為值列，將根據此列對最終輸出中返回的列（稱為分組列）進行分組。在本例中，通過
COUNT 函數彙總分組列。請注意，將顯示一條警告訊息，指出為每個僱員計算
COUNT 時未考慮顯示在 PurchaseOrderID 列中的任何空值。

重要提示
如果彙總函式與 PIVOT 一起使用，則計算彙總時將不考慮出現在值列中的任何空值。

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);GOINSERT 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, OrdersFROM    (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5   FROM pvt) pUNPIVOT   (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 的輸入中的空值不會顯示在輸出中，而在執行 PIVOT 操作之前，輸入中可能有原始的空值。

AdventureWorks2008R2 樣本資料庫中的 Sales.vSalesPersonSalesByFiscalYears 視圖將使用 PIVOT 返回每個銷售人員在每個會計年度的總銷售額。若要在 SQL Server Management Studio 中編寫視圖指令碼，請在“物件總管”中，在“視圖”檔案夾下找到 AdventureWorks2008R2 資料庫對應的視圖。按右鍵該視圖名稱，再選擇“編寫視圖指令碼為”。