SQL Server 行轉列 (PIVOT) 完整實戰教學

SQL Server PIVOT/UNPIVOT 行列轉換核心技術

行轉列（PIVOT）與列轉行（UNPIVOT）是報表開發、資料分析、跨表查詢的必備技能。SQL Server 2005+ 內建 PIVOT/UNPIVOT 運算子，配合動態 SQL 可解決列名不固定的複雜場景。

靜態 PIVOT — 固定列值場景

基礎語法

SELECT ,
    [pivot_value1] AS alias1, [pivot_value2] AS alias2, ...
FROM (
    SELECT  FROM table_source
) AS source_table
PIVOT (
    aggregate_function(value_column)
    FOR pivot_column IN ([pivot_value1], [pivot_value2], ...)
) AS pivot_table

實戰範例：銷售資料行轉列

-- 原始資料
-- year | quarter | amount
-- 2023 | Q1      | 100
-- 2023 | Q2      | 150
-- 2024 | Q1      | 200

SELECT year, [Q1] AS Q1_sales, [Q2] AS Q2_sales, [Q3] AS Q3_sales, [Q4] AS Q4_sales
FROM (
    SELECT year, quarter, amount FROM sales_data
) src
PIVOT (
    SUM(amount) FOR quarter IN ([Q1], [Q2], [Q3], [Q4])
) pvt
-- 結果
-- year | Q1_sales | Q2_sales | Q3_sales | Q4_sales
-- 2023 | 100      | 150      | NULL     | NULL
-- 2024 | 200      | NULL     | NULL     | NULL

動態 PIVOT — 列值不固定/未知場景

當轉換後的列名來自資料本身（如產品名稱、部門、日期），需動態構建 SQL。

動態 PIVOT 模板

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX)

-- 1. 取得唯一列值並拼接
SELECT @columns = STRING_AGG(QUOTENAME(category), ',')
FROM (SELECT DISTINCT category FROM products) t
-- SQL Server 2017+ 使用 STRING_AGG，舊版用 STUFF + FOR XML PATH

-- 2. 構建動態 SQL
SET @sql = N'
SELECT product_name, ' + @columns + '
FROM (
    SELECT product_name, category, sales_amount
    FROM monthly_sales
) src
PIVOT (
    SUM(sales_amount) FOR category IN (' + @columns + ')
) pvt'

-- 3. 執行
EXEC sp_executesql @sql

UNPIVOT 列轉行 — 寬表轉長表

SELECT product_name, quarter, sales
FROM (
    SELECT product_name, Q1, Q2, Q3, Q4 FROM product_sales
) src
UNPIVOT (
    sales FOR quarter IN (Q1, Q2, Q3, Q4)
) unpvt
-- 結果：每產品每季一行

進階技巧與注意事項

處理 NULL 值顯示

SELECT year, 
    ISNULL([Q1], 0) AS Q1, 
    ISNULL([Q2], 0) AS Q2
FROM ... PIVOT ...

多欄位聚合

PIVOT 僅支援單一聚合欄位。需多欄位時，可用條件式聚合替代：

SELECT year,
    SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS Q1_amt,
    SUM(CASE WHEN quarter = 'Q1' THEN qty END) AS Q1_qty,
    SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS Q2_amt
FROM sales_data
GROUP BY year

效能優化建議

• 來源查詢加上 WHERE 過濾，減少參與 PIVOT 的行數
• pivot_column 建立索引
• 動態 PIVOT 緩存執行計畫（參數化）
• 大資料量考慮先彙總再 PIVOT

常見錯誤排查

1. 列名包含特殊字元：必須用 `QUOTENAME` 或 `[]` 包裹
2. 聚合函數缺失：PIVOT 必須指定 SUM/COUNT/AVG 等
3. 來源欄位過多：子查詢只選需要的三欄（群組鍵、樞紐鍵、值鍵）
4. 動態 SQL 注入風險：列值來自使用者輸入時，必須驗證白名單