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

SQL 行列轉換兩大神技：CASE WHEN 條件聚合 vs PIVOT 函數

編輯：MySQL 資料庫相關知識

測試資料準備

CREATE TABLE Scores (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Student VARCHAR(20),
    Subject VARCHAR(30),
    Score FLOAT
);
INSERT INTO Scores VALUES
('test001','語文',90),('test001','英語',85),
('test002','語文',90),('test002','英語',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 將目標科目分數投影到對應欄位，其他科目歸 0；MAX 聚合去除 0 保留實際分數 (假設分數非負)。

方法二：PIVOT 運算子 (SQL Server 2005+、Oracle 11g+)

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 (目標值清單))

對比與選型建議

維度	CASE WHEN	PIVOT
相容性	所有主流 RDBMS	SQL Server/Oracle/PostgreSQL(crosstab)
動態欄位	需動態 SQL 拼接	同樣需動態 SQL
可讀性	直觀、易偵錯	語法緊湊但較抽象
效能	相近	內部優化略優
多聚合	支援 SUM/MAX/AVG 混用	單一聚合函數

進階：動態行列轉換 (以 CASE WHEN 為例)

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 (SELECT Student, Subject, Score FROM Scores) src PIVOT (AVG(Score) FOR Subject IN (' + @cols + N')) pvt';
EXEC sp_executesql @sql;

適用於科目不固定、需自動適應新增科目的報表場景。