SQL 行轉列 (PIVOT) 完全指南：靜態、動態與 Access/Oracle 寫法對比

前言

行轉列 (PIVOT) 是報表開發中極高頻的需求：將「長表」(鍵值對列) 轉為「寬表」(多欄位橫向展示)。本文涵蓋 SQL Server (靜態/動態 PIVOT)、Access (IIF)、Oracle (DECODE/PIVOT) 多平台寫法，並進階示範「含總計/平均」的複雜報表邏輯。

一、基礎場景：學生成績表行轉列

原始資料 (tb)：

目標結果：

二、SQL Server 解法

1. CASE WHEN 聚合 (通用、全版本支援)

-- 靜態：課程固定
SELECT 
    姓名,
    MAX(CASE WHEN 課程 = '語文' THEN 分數 ELSE 0 END) AS 語文,
    MAX(CASE WHEN 課程 = '數學' THEN 分數 ELSE 0 END) AS 數學,
    MAX(CASE WHEN 課程 = '物理' THEN 分數 ELSE 0 END) AS 物理
FROM tb
GROUP BY 姓名;

2. 動態 SQL 拼接 (課程不固定)

DECLARE @sql NVARCHAR(MAX) = N'SELECT 姓名';
SELECT @sql += N', MAX(CASE WHEN 課程 = ' + QUOTENAME(課程, '''') + N' THEN 分數 ELSE 0 END) AS ' + QUOTENAME(課程)
FROM (SELECT DISTINCT 課程 FROM tb) AS d;
SET @sql += N' FROM tb GROUP BY 姓名';
EXEC sp_executesql @sql;

關鍵：QUOTENAME(課程, '''') 安全處理單引號與特殊字元，防注入。

3. PIVOT 運算子 (SQL Server 2005+)

-- 靜態 PIVOT
SELECT * FROM (
    SELECT 姓名, 課程, 分數 FROM tb
) AS src
PIVOT (MAX(分數) FOR 課程 IN ([語文], [數學], [物理])) AS pvt;

-- 動態 PIVOT
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(課程), ',') FROM (SELECT DISTINCT 課程 FROM tb) d; -- 2017+ STRING_AGG
-- 舊版用 STUFF + FOR XML PATH 拼接
SET @sql = N'SELECT * FROM (SELECT 姓名, 課程, 分數 FROM tb) t PIVOT (MAX(分數) FOR 課程 IN (' + @cols + N')) p';
EXEC sp_executesql @sql;

三、Access 寫法：IIF 替代 CASE

Access 不支援 CASE，需用 IIF：

SELECT 
    姓名,
    SUM(IIF(課程='語文', 分數, 0)) AS 語文,
    SUM(IIF(課程='數學', 分數, 0)) AS 數學,
    SUM(IIF(課程='物理', 分數, 0)) AS 物理
FROM tb
GROUP BY 姓名;

或簡單單行轉換：SELECT IIF(sex=1, '男', '女') FROM tablename

四、Oracle 寫法

-- 1. DECODE (傳統)
SELECT 姓名,
    MAX(DECODE(課程, '語文', 分數)) 語文,
    MAX(DECODE(課程, '數學', 分數)) 數學,
    MAX(DECODE(課程, '物理', 分數)) 物理
FROM tb GROUP BY 姓名;

-- 2. CASE WHEN (標準)
SELECT 姓名,
    MAX(CASE 課程 WHEN '語文' THEN 分數 END) 語文, ...
FROM tb GROUP BY 姓名;

-- 3. PIVOT (11g+)
SELECT * FROM (
    SELECT 姓名, 課程, 分數 FROM tb
)
PIVOT (MAX(分數) FOR 課程 IN ('語文' AS 語文, '數學' AS 數學, '物理' AS 物理));

五、進階：增加「平均分」與「總分」欄位

目標：

SQL Server 方案 (CTE + 計算欄位)

WITH PivotData AS (
    SELECT 
        姓名,
        MAX(CASE WHEN 課程 = '語文' THEN 分數 END) AS 語文,
        MAX(CASE WHEN 課程 = '數學' THEN 分數 END) AS 數學,
        MAX(CASE WHEN 課程 = '物理' THEN 分數 END) AS 物理
    FROM tb
    GROUP BY 姓名
)
SELECT 
    姓名, 語文, 數學, 物理,
    ROUND((ISNULL(語文,0) + ISNULL(數學,0) + ISNULL(物理,0)) / 3.0, 2) AS 平均分,
    ISNULL(語文,0) + ISNULL(數學,0) + ISNULL(物理,0) AS 總分
FROM PivotData;

動態 SQL 版 (自動包含所有課程)

DECLARE @cols NVARCHAR(MAX), @selectCols NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- 1. 取得所有課程欄位清單
SELECT @cols = STRING_AGG(QUOTENAME(課程), ',') 
FROM (SELECT DISTINCT 課程 FROM tb) d;

-- 2. 建構 SELECT 清單：課程欄位 + 平均 + 總分
SELECT @selectCols = STRING_AGG(
    N'ISNULL(' + QUOTENAME(課程) + N', 0)', ' + ') 
FROM (SELECT DISTINCT 課程 FROM tb) d;
SET @selectCols = N'姓名, ' + @cols + N', 
    ROUND((' + @selectCols + N') / ' + CAST((SELECT COUNT(DISTINCT 課程) FROM tb) AS NVARCHAR) + N'.0, 2) AS 平均分,
    (' + @selectCols + N') AS 總分';

-- 3. 完整動態 PIVOT
SET @sql = N'SELECT ' + @selectCols + N' FROM (
    SELECT 姓名, 課程, 分數 FROM tb
) t PIVOT (MAX(分數) FOR 課程 IN (' + @cols + N')) p';
EXEC sp_executesql @sql;

六、效能與避坑指南

• 聚合函數選擇：分數類數值用 MAX 或 SUM (單值時等價)；字串類用 MAX。
• NULL 處理：PIVOT 結果預設為 NULL，外層查詢務必用 ISNULL/COALESCE 處理，否則總分計算會變 NULL。
• 索引建議：(名, 課程) 複合索引可加速分組與轉換。
• 動態 SQL 安全：永遠使用 QUOTENAME 包裹識別碼，參數化傳值，杜絕注入風險。

總結

行轉列核心邏輯是「分組 + 條件聚合」。SQL Server 推薦 PIVOT 語法簡潔；動態場景下 CASE WHEN 拼接靈活度最高；Access 用 IIF，Oracle 用 DECODE/PIVOT。掌握「靜態寫死 → 動態拼接 → 進階統計欄位」三層遞進，即可應對 99% 報表轉換需求。