面試常考 SQL 題目：行轉列 (Pivot) 實作

面試經典題：將縱向成績表轉為橫向顯示。

原始資料

CREATE TABLE cj (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(20),
  subject VARCHAR(20),
  result INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO cj (name, subject, result) VALUES
('張三','語文',80),('張三','數學',90),('張三','物理',85),
('李四','語文',85),('李四','數學',92),('李四','物理',82);

目標結果

姓名  語文  數學  物理
張三   80   90   85
李四   85   92   82

解法一：條件聚合 (標準 SQL、效能最佳)

SELECT
  name AS 姓名,
  MAX(CASE WHEN subject = '語文' THEN result END) AS 語文,
  MAX(CASE WHEN subject = '數學' THEN result END) AS 數學,
  MAX(CASE WHEN subject = '物理' THEN result END) AS 物理
FROM cj
GROUP BY name;

解法二：多表自聯結 (舊版 MySQL 常用)

SELECT
  c.name AS 姓名,
  c1.result AS 語文,
  c2.result AS 數學,
  c3.result AS 物理
FROM (SELECT DISTINCT name FROM cj) c
LEFT JOIN cj c1 ON c.name = c1.name AND c1.subject = '語文'
LEFT JOIN cj c2 ON c.name = c2.name AND c2.subject = '數學'
LEFT JOIN cj c3 ON c.name = c3.name AND c3.subject = '物理';

解法三：動態列 (科目不固定) - 需預處理/動態 SQL

-- 1. 取得所有科目
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN subject = ''', subject, ''' THEN result END) AS ', subject)) INTO @cols FROM cj;
-- 2. 組裝完整 SQL
SET @sql = CONCAT('SELECT name AS 姓名, ', @cols, ' FROM cj GROUP BY name');
-- 3. 執行
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

MySQL 8.0+ 新語法 (非標準，僅參考)

SELECT * FROM cj PIVOT (MAX(result) FOR subject IN ('語文','數學','物理'));
-- 注意：MySQL 8.0 目前不支援 PIVOT，上述為 SQL Server/Oracle 語法

面試加分點：說明 條件聚合單掃描、可用索引、擴充性佳，並能手寫動態 SQL 版本。