FROM - 使用 PIVOT 和 UNPIVOT
適用於：
SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體Azure Synapse AnalyticsAnalytics Platform System (PDW)
您可以使用 PIVOT 和 UNPIVOT 關係運算子，將資料表值運算式變更為另一個資料表。 PIVOT 會透過將唯一值從運算式中的某一資料行轉換為輸出中的多個資料行，來旋轉表格值運算式。 而 PIVOT 會在最終輸出中需要它們的任何其餘資料行上執行彙總。 UNPIVOT 執行的作業與 PIVOT 相反，它會將資料表值運算式的資料行旋轉成資料行值。
的 PIVOT 語法比在複雜數 SELECT...CASE 列語句中所指定的語法簡單且更容易閱讀。 如需 PIVOT 語法的完整描述，請參閱 FROM (Transact-SQL)。
語法
下列語法摘要說明如何使用 PIVOT 運算子。
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>;
備註
UNPIVOT 子句中的資料行識別碼會依照目錄定序。 SQL Database 的定序一律為 SQL_Latin1_General_CP1_CI_AS。 SQL Server 部分自主資料庫的定序一律為 Latin1_General_100_CI_AS_KS_WS_SC。 如果資料行與其他資料行結合，就必須使用定序子句 (COLLATE DATABASE_DEFAULT) 來避免衝突。
在 Microsoft Fabric 和 Azure Synapse Analytics 集區中，如果 PIVOT 在非樞紐資料行輸出上有 GROUP BY，具有 PIVOT 運算子的查詢就會失敗。 因應措施是，從 GROUP BY 移除非樞紐資料行。 查詢結果相同，因為這個 GROUP BY 子句重複。
基本 PIVOT 範例
下列程式碼範例會產生包含兩個資料行的資料表，其中有四個資料列。
USE AdventureWorks2022;
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 值變成了資料行的標題。 即使結果為 [3]，還是為這三 NULL 天產生了一個資料行。
-- 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 的用處。 例如，假設您想要查詢 PurchaseOrderHeader 範例資料庫中的 AdventureWorks2022 資料表，以判斷某些員工所下的訂單數目。 下列查詢會提供這個報表，並依供應商排序：
USE AdventureWorks2022;
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 編號都會有一個資料行：在此情況下是員工 250、251、256、257 和 260。 PurchaseOrderID 資料行會當作數值資料行，這是在最終輸出中傳回的資料行 (稱為群組資料行) 所根據以進行分組的資料行。 在此情況下，COUNT 函數會對群組資料行進行彙總。 請注意，此時會顯示警告訊息，指出計算每個員工的 PurchaseOrderID 時，並未考慮 COUNT 資料行中出現的任何 Null 值。
重要
搭配 PIVOT 使用彙總函數時，在計算彙總期間不會考慮值資料行中出現的任何 Null 值。
UNPIVOT 範例
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 值。
AdventureWorks2022 範例資料庫中的 Sales.vSalesPersonSalesByFiscalYears 檢視表會使用 PIVOT 來傳回每位銷售人員在每個會計年度的總銷售額。 若要在 SQL Server Management Studio 中編寫該檢視的指令碼，請在物件總管中，於 AdventureWorks2022 資料庫的 [檢視] 資料夾底下找出該檢視。 在檢視表名稱上按一下滑鼠右鍵，然後選取 [編寫檢視表的指令碼為] 。