SQL Server 2005 PIVOT/UNPIVOT 運算子實戰：資料行列互轉完全指南

前置需求

資料庫相容性層級必須設為 90 (SQL Server 2005) 以上：

EXEC sp_dbcmptlevel 'YourDB', 90;

語法結構

-- PIVOT: 行轉列 (聚合)
SELECT <非透視欄位>, [透視值清單]
FROM <來源表>
PIVOT (
    <聚合函數>(<值欄位>)
    FOR <透視欄位> IN ([值1], [值2], ...)
) AS <別名>;

-- UNPIVOT: 列轉行
SELECT <非透視欄位>, <透視欄位>, <值欄位>
FROM <來源表>
UNPIVOT (
    <值欄位> FOR <透視欄位> IN ([欄位1], [欄位2], ...)
) AS <別名>;

關鍵字段說明

• aggregate_function：系統/使用者定義聚合函數，**不可使用 COUNT(*)**。
• value_column：PIVOT 計算依據欄位；UNPIVOT 為輸出值欄位名稱 (不可與來源表現有欄位同名)。
• pivot_column：PIVOT 為來源欄位 (需可轉換為 nvarchar)；UNPIVOT 為輸出欄位名稱 (不可與來源表同名)。
• IN (column_list)：PIVOT 列出將成為輸出欄位的值；UNPIVOT 列出將被縮減為單一欄位的來源欄位。

經典應用：交叉表報表產生

來源資料 (正規化儲存) → PIVOT → 交叉表 (人類閱讀友善)

範例：月份 x 產品銷售額交叉表

-- 來源表 SalesData (Year, Month, Product, Amount)
SELECT Product, 
       ISNULL([1],0) AS Jan, ISNULL([2],0) AS Feb, ISNULL([3],0) AS Mar,
       ISNULL([4],0) AS Apr, ISNULL([5],0) AS May, ISNULL([6],0) AS Jun,
       ISNULL([7],0) AS Jul, ISNULL([8],0) AS Aug, ISNULL([9],0) AS Sep,
       ISNULL([10],0) AS Oct, ISNULL([11],0) AS Nov, ISNULL([12],0) AS Dec
FROM SalesData
PIVOT (SUM(Amount) FOR Month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS Pvt
ORDER BY Product;

動態 PIVOT：未知欄位值時

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(Month), ',') 
FROM (SELECT DISTINCT Month FROM SalesData) AS M; -- SQL 2017+，舊版用 FOR XML PATH

SET @sql = N'SELECT Product, ' + @cols + N'
FROM SalesData
PIVOT (SUM(Amount) FOR Month IN (' + @cols + N')) AS Pvt';
EXEC sp_executesql @sql;

UNPIVOT 實例：將交叉表還原正規化

SELECT Year, Product, Month, Amount
FROM CrossTabSales
UNPIVOT (Amount FOR Month IN (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)) AS Unpvt;

效能與限制

• PIVOT 內部實作為 Group By + Case 條件聚合，大量欄位值會產生龐大查詢計畫。
• 透視欄位值建議預先篩選或限制數量，避免動態 SQL 注入風險。
• UNPIVOT 不會還原被 PIVOT 聚合過的明細資料 (資訊已遺失)。
• SQL Server 2016+ 可考慮 JSON / OPENJSON 進行彈性行列轉換。