PIVOT 行列轉換 (SQL PIVOT)

PIVOT 是一種資料轉換操作，可以將資料從列 (rows) 轉換成欄 (columns)，常用於製作交叉分析報表、彙總表或樞紐分析表。它將原本垂直排列的資料「旋轉」成水平排列的欄位。

PIVOT 基本概念

轉換前 (列式資料)：

轉換後 (PIVOT 結果)：

季度 (Quarter) 的值變成了欄位名稱，對應的金額 (Amount) 則成為該欄位的值。

SQL Server PIVOT 語法 (Syntax)

SELECT pivot_column, [value1], [value2], ...
FROM (
    SELECT grouping_column, pivot_column, value_column
    FROM source_table
) AS source
PIVOT (
    aggregate_function(value_column)
    FOR pivot_column IN ([value1], [value2], ...)
) AS pivot_table;

關鍵要素：

• aggregate_function：聚合函數，如 SUM、COUNT、AVG 等
• value_column：要聚合的數值欄位
• pivot_column：要轉換成欄位名稱的欄位
• [value1], [value2], ...：pivot_column 中要轉換成欄位的具體值

SQL Server PIVOT 範例 (Example)

sales 資料表：

範例： 將產品名稱轉換為欄位，顯示各區域的產品銷售額。

SELECT Region, [LCD], [CPU]
FROM (
    SELECT Region, Product, Amount
    FROM sales
) AS source
PIVOT (
    SUM(Amount)
    FOR Product IN ([LCD], [CPU])
) AS pivot_table;

結果：

MySQL 的 PIVOT 替代方案

⚠️ 注意： MySQL 沒有內建 PIVOT 語法。但可以使用 CASE 搭配 GROUP BY 和聚合函數達到相同效果。

SELECT
    Region,
    SUM(CASE WHEN Product = 'LCD' THEN Amount ELSE 0 END) AS LCD,
    SUM(CASE WHEN Product = 'CPU' THEN Amount ELSE 0 END) AS CPU
FROM sales
GROUP BY Region;

這個方法在所有支援 CASE 的資料庫中都適用。

多欄位 PIVOT

可以同時對多個欄位進行轉換：

-- 先建立子查詢組合欄位
SELECT Region,
       [LCD_2023], [LCD_2024], [CPU_2023], [CPU_2024]
FROM (
    SELECT Region,
           Product + '_' + CAST(Year AS VARCHAR) AS Product_Year,
           Amount
    FROM sales
) AS source
PIVOT (
    SUM(Amount)
    FOR Product_Year IN ([LCD_2023], [LCD_2024], [CPU_2023], [CPU_2024])
) AS pivot_table;

動態 PIVOT (Dynamic PIVOT)

當轉換的欄位值是動態的 (事先不知道有哪些值) 時，需要使用動態 SQL：

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

-- 步驟 1：取得所有不重複的產品名稱
SELECT @columns = STRING_AGG(QUOTENAME(Product), ', ')
FROM (SELECT DISTINCT Product FROM sales) AS products;

-- 步驟 2：組合動態 SQL
SET @sql = N'
SELECT Region, ' + @columns + '
FROM (
    SELECT Region, Product, Amount
    FROM sales
) AS source
PIVOT (
    SUM(Amount)
    FOR Product IN (' + @columns + ')
) AS pivot_table';

-- 步驟 3：執行動態 SQL
EXEC sp_executesql @sql;

⚠️ 安全提示： 動態 SQL 存在 SQL Injection 風險。在實務中應對輸入資料進行驗證，並考慮使用參數化查詢。

UNPIVOT (反向操作)

UNPIVOT 是 PIVOT 的反向操作，將欄位轉換回列。

轉換前：

UNPIVOT 後：

SQL Server UNPIVOT 語法：

SELECT Region, Product, Amount
FROM pivot_result
UNPIVOT (
    Amount FOR Product IN ([LCD], [CPU])
) AS unpivot_table;

MySQL/其他資料庫的 UNPIVOT 替代方案：

使用 UNION ALL：

SELECT Region, 'LCD' AS Product, LCD AS Amount FROM pivot_result
UNION ALL
SELECT Region, 'CPU' AS Product, CPU AS Amount FROM pivot_result;

實際應用場景

1. 銷售報表

按月份顯示各產品的銷售額：

SELECT Product, [Jan], [Feb], [Mar], [Apr], ...
FROM sales_data
PIVOT (SUM(Amount) FOR Month IN ([Jan], [Feb], ...)) AS monthly_sales;

2. 問卷調查統計

將問卷答案轉換成各選項的統計數：

SELECT Question,
       [非常滿意], [滿意], [普通], [不滿意], [非常不滿意]
FROM survey_results
PIVOT (COUNT(Response_Id) FOR Answer IN (...)) AS survey_stats;

3. 交叉分析

分析不同維度的資料交叉關係。

資料庫支援

相關主題

• CASE - 條件判斷表達式
• GROUP BY - 資料分組
• 聚合函數 - SUM, COUNT, AVG 等