FROM - 使用 PIVOT 和 UNPIVOT

發行項
11/19/2024

適用於：Microsoft Fabric SQL 資料庫中Microsoft網狀架構倉儲中的 SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System （PDW） SQL 分析端點Microsoft網狀架構

您可以使用 PIVOT 和 UNPIVOT 關係運算子，將資料表值運算式變更為另一個資料表。 PIVOT 會透過將唯一值從運算式中的某一資料行轉換為輸出中的多個資料行，來旋轉表格值運算式。 PIVOT 也會在最終輸出中所需的任何剩餘數據行值上執行匯總。 UNPIVOT 藉由將數據表值表達式的數據行旋轉成數據行值，對執行相反的作業 PIVOT。

的 PIVOT 語法比在複雜數 SELECT...CASE 列語句中可能指定的語法更容易且更容易閱讀。如需語法 PIVOT的完整描述，請參閱 FROM子句。

注意

在單一 T-SQL 語句內重複使用 PIVOT/UNPIVOT 可能會對查詢效能造成負面影響。

本文 Transact-SQL 程式碼範例使用 AdventureWorks2022 或 AdventureWorksDW2022 範例資料庫，從 Microsoft SQL Server Samples 和 Community Projects （Microsoft SQL Server 範例和社群專案）首頁即可下載。

語法

本節摘要說明如何使用 PIVOT 和 UNPIVOT 運算符。

運算子的 PIVOT 語法。

SELECT [ <non-pivoted column> [ AS <column name> ] , ]
    ...
    [ <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 become column headers>
    IN ( <first pivoted column>
         , <second pivoted column>
         , ... <last pivoted column> )
) AS <alias for the pivot table>
[ <optional ORDER BY clause> ]
[ ; ]

運算子的 UNPIVOT 語法。

SELECT [ <non-pivoted column> [ AS <column name> ] , ]
    ...
    [ <output column for names of the pivot columns> [ AS <column name> ] , ]
    [ <new output column created for values in result of the source query> [ AS <column name> ] ]
FROM
    ( <SELECT query that produces the data> )
    AS <alias for the source query>
UNPIVOT
(
    <new output column created for values in result of the source query>
FOR <output column for names of the pivot columns>
    IN ( <first pivoted column>
         , <second pivoted column>
         , ... <last pivoted column> )
)
[ <optional ORDER BY clause> ]
[ ; ]

備註

UNPIVOT 子句中的資料行識別碼會依照目錄定序。

對於 Azure SQL 資料庫，定序一律SQL_Latin1_General_CP1_CI_AS為。
SQL Server 部分自主資料庫的定序一律為 Latin1_General_100_CI_AS_KS_WS_SC。

如果資料行與其他資料行結合，就必須使用定序子句 (COLLATE DATABASE_DEFAULT) 來避免衝突。

在 Microsoft Fabric 和 Azure Synapse Analytics 集區中，如果 GROUP BY 的非樞紐數據行輸出PIVOT有，則運算符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

未定義任何產品，其值為 3 DaysToManufacture。

下列程式碼會顯示同樣的結果，但是經過樞紐處理後，讓 DaysToManufacture 值變成了資料行的標題。即使結果為 NULL，仍會提供三天的數據[3]行。

-- Pivot table with one row and five columns
SELECT 'AverageCost' AS CostSortedByProductionDays,
    [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;

結果集如下所示。

CostSortedByProductionDays  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、、 256257和 260 在此範例中。 PurchaseOrderID 資料行會當作數值資料行，這是在最終輸出中傳回的資料行 (稱為群組資料行) 所根據以進行分組的資料行。在此情況下，COUNT 函數會對群組資料行進行彙總。出現警告訊息，指出計算COUNT每位員工時，不會考慮數據行中PurchaseOrderID出現的任何 Null 值。

重要

當聚合函數與 PIVOT搭配使用時，計算匯總時，不會考慮值數據行中任何 Null 值的存在。

UNPIVOT 範例

UNPIVOT 執行的作業則幾乎與 PIVOT 相反，它會將資料行旋轉成資料列。假設上述範例中所產生的資料表在資料庫中是儲存為 pvt，而現在您想要將資料行識別碼 Emp1、Emp2、Emp3、Emp4 和 Emp5 旋轉成對應到特定供應商的資料列值。因此，您必須識別兩個額外的數據行。

包含您要旋轉之資料列值的資料列值會Emp1Emp2呼叫 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 不會重現原始數據表值表達式結果，因為數據列已經合併。此外， NULL 在輸出中消失的輸入 UNPIVOT 值。當值消失時，它會顯示作業之前PIVOT輸入中可能有原始NULL值。

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

共用方式為

FROM - 使用 PIVOT 和 UNPIVOT

語法

備註

基本 PIVOT 範例

複雜 PIVOT 範例

UNPIVOT 範例

意見反應

其他資源

共用方式為

FROM - 使用 PIVOT 和 UNPIVOT

語法

備註

基本 PIVOT 範例

複雜 PIVOT 範例

UNPIVOT 範例

相關內容

意見反應

其他資源