SQL CASE WHEN 搭配 MAX/GROUP BY 實現行轉列 (透視表) 原理詳解

資料模型

Student(S#, Sname)
Course(C#, Cname)
SC(S#, C#, Score)

目標輸出

S#  Sname  語文  數學  英語
01  趙雷   80   90   99
02  錢電   70   60   80

SQL 語句

SELECT a.S# 學生編號, a.Sname 學生姓名,
    MAX(CASE c.Cname WHEN N'語文' THEN b.Score END) [語文],
    MAX(CASE c.Cname WHEN N'數學' THEN b.Score END) [數學],
    MAX(CASE c.Cname WHEN N'英語' THEN b.Score END) [英語]
FROM Student a
LEFT JOIN SC b ON a.S# = b.S#
LEFT JOIN Course c ON b.C# = c.C#
GROUP BY a.S#, a.Sname
ORDER BY a.S#;

執行邏輯逐步拆解

1. JOIN 後中間結果 (每生成一行一科)：
   S# Sname Cname Score
01 趙雷 語文 80
01 趙雷 數學 90
01 趙雷 英語 99
02 錢電 語文 70...
2. CASE WHEN 執行：對每一行，只有符合科目的行回傳分數，其餘回傳 NULL。
   例：第一行 CASE WHEN '語文' THEN 80 END → 80，同行 數學/英語 → NULL。
3. GROUP BY 學生：將同一學生的多行合併為一組。
4. MAX 聚合：每組中，語文 欄只有唯一一行有值 (80)，其餘 NULL，MAX(80, NULL, NULL) = 80。同理數學、英語。

關鍵點

• MAX (或 SUM/MIN) 將「稀疏矩陣」壓縮為單行。
• CASE WHEN ... THEN score END 隱含 ELSE NULL，NULL 不參與聚合計算。
• 若一生一科有多筆成績，MAX 取最高分，SUM 會加總，需依業務選擇聚合函數。

動態列擴展 (進階)

科目不固定時，需動態構建 SQL (預存程序/應用層拼接) 或使用資料庫特有透視語法：
- SQL Server: PIVOT
- Oracle: PIVOT / LISTAGG
- PostgreSQL: crosstab (tablefunc)
- MySQL: 僅支援動態 SQL 拼接