PIVOT 行列轉換 (SQL PIVOT)

PIVOT 是一種資料轉換操作，可以將資料列 (rows) 轉換成欄位 (columns)，常用於製作交叉分析報表或彙總表。

PIVOT 基本概念

假設有以下銷售資料：

透過 PIVOT 可以轉換成：

SQL Server PIVOT 語法

SELECT *
FROM table_name
PIVOT (
    aggregate_function(value_column)
    FOR pivot_column IN ([value1], [value2], ...)
) AS pivot_table;

SQL Server PIVOT 範例 (Example)

假設我們有一個 sales 資料表：

使用 PIVOT 將產品名稱轉為欄位：

SELECT Region, [LCD], [CPU]
FROM sales
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;

UNPIVOT

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

SQL Server 語法：

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

動態 PIVOT

當轉換的欄位值是動態的（事先不知道有哪些值），可以使用動態 SQL：

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

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

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

EXEC sp_executesql @sql;