您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

SQL 行列轉換實戰：CASE WHEN 與 PIVOT 兩大核心技法

編輯：MySQL 資料庫相關知識

測試資料準備

CREATE DATABASE tmp;
USE tmp;

CREATE TABLE Scores (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Student VARCHAR(20),
    Subject VARCHAR(30),
    Score FLOAT
);

TRUNCATE TABLE Scores;
INSERT INTO Scores (Student, Subject, Score) VALUES
('test001', '語文', 90),
('test001', '英語', 85),
('text002', '語文', 90),
('text002', '英語', 80),
('test003', '語文', 95),
('test003', '英語', 85);

原始資料為「長表」：每列一科成績。

方法一：CASE WHEN + 聚合函數（通用標準寫法）

SELECT
    Student AS 姓名,
    MAX(CASE Subject WHEN '語文' THEN Score ELSE 0 END) AS 語文,
    MAX(CASE Subject WHEN '英語' THEN Score ELSE 0 END) AS 英語
FROM Scores
GROUP BY Student
ORDER BY Student;

原理： 按學生分組，利用 CASE 將科目轉為列，MAX 聚合取非零值。支援所有主流資料庫（MySQL、PostgreSQL、Oracle、SQL Server）。

方法二：PIVOT 運算子（SQL Server / Oracle 專用）

SELECT
    Student AS 姓名,
    AVG(語文) AS 語文,
    AVG(英語) AS 英語
FROM Scores
PIVOT (AVG(Score) FOR Subject IN (語文, 英語)) AS NewScores
GROUP BY Student
ORDER BY Student ASC;

語法結構：

PIVOT (聚合函數(值欄位) FOR 樞紐欄位 IN (目標列清單))

優點：語意清晰、效能略優；缺點：目標列需硬編碼，動態列需動態 SQL。

結果對比

姓名	語文	英語
test001	90	85
test003	95	85
text002	90	80

進階：動態行列轉換

科目不固定時，需先查出所有科目，拼接動態 SQL 執行（以 SQL Server 為例）：

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(Subject), ',') FROM (SELECT DISTINCT Subject FROM Scores) t;
SET @sql = N'SELECT Student, ' + @cols + N' FROM Scores PIVOT (AVG(Score) FOR Subject IN (' + @cols + N')) p';
EXEC sp_executesql @sql;

MySQL 8.0+ 可用 JSON_TABLE 或預處理語句實現類似效果。