SQL Server 行轉列 (PIVOT) 與列轉行 (UNPIVOT) 完整實戰：CASE 聚合 vs 原生語法對比

行轉列 (將列值變為欄位名) 是報表常見需求。傳統寫法用 `CASE ... END` 搭配聚合函數；SQL Server 2005+ 提供原生 `PIVOT`/`UNPIVOT` 語法。本文以學生成績表為例，對比兩種寫法並示範雙向轉換。

測試資料

DECLARE @t TABLE (StudentName NVARCHAR(20), Subject NVARCHAR(20), Score INT);
INSERT @t VALUES
('學生A','中文',80),('學生A','數學',78),('學生A','英語',92),
('學生B','中文',89),('學生B','數學',87),('學生B','英語',75),
('學生C','中文',92),('學生C','數學',74),('學生C','英語',65),
('學生D','中文',79),('學生D','數學',83),('學生D','英語',81),
('學生E','中文',73),('學生E','數學',84),('學生E','英語',93),
('學生F','中文',79),('學生F','數學',86),('學生F','英語',84);

方法一：CASE + 聚合函數 (通用、可控性高)

SELECT 
    StudentName,
    SUM(CASE WHEN Subject = N'中文' THEN Score ELSE 0 END) AS Chinese,
    SUM(CASE WHEN Subject = N'數學' THEN Score ELSE 0 END) AS Math,
    SUM(CASE WHEN Subject = N'英語' THEN Score ELSE 0 END) AS English
FROM @t
GROUP BY StudentName;

• 優點：標準 SQL、可自訂輸出欄位順序/別名、支援複雜條件邏輯。
• 缺點：需預知所有類別值、程式碼較冗長。

方法二：原生 PIVOT (簡潔、動態類別需動態 SQL)

語法結構

SELECT <非轉換欄位>, [轉換後欄位清單]
FROM (SELECT <來源欄位> FROM 來源表) AS src
PIVOT (
    聚合函數(值欄位) 
    FOR 轉軸欄位 IN ([欄位1], [欄位2], ...)
) AS pvt;

實戰範例

SELECT 
    StudentName, 
    [中文] AS Chinese, 
    [數學] AS Math, 
    [英語] AS English
FROM (SELECT * FROM @t) AS src
PIVOT (
    SUM(Score) FOR Subject IN ([中文], [英語], [數學])
) AS pvt;

關鍵點： `IN` 清單順序決定輸出欄位順序；欄位名含特殊字元需用 `[]` 括起。

UNPIVOT：列轉行 (逆向操作)

SELECT StudentName, Subject, Score
FROM (SELECT * FROM @t) AS src
PIVOT (SUM(Score) FOR Subject IN ([中文],[英語],[數學])) AS pvt
UNPIVOT (
    Score FOR Subject IN ([中文],[英語],[數學])
) AS unpvt;

輸出還原為原始長表格式 (StudentName, Subject, Score)。

動態 PIVOT (類別值不固定時)

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(Subject), ',')  -- SQL 2017+
       FROM (SELECT DISTINCT Subject FROM @t) t;
SET @sql = N'
SELECT StudentName, ' + @cols + '
FROM (SELECT StudentName, Subject, Score FROM @t) src
PIVOT (SUM(Score) FOR Subject IN (' + @cols + ')) pvt;';
EXEC sp_executesql @sql;

SQL Server 2016 以前用 `STUFF` + `FOR XML PATH` 組裝 `@cols`。

選擇建議

掌握 `PIVOT`/`UNPIVOT` 原生語法與 `CASE` 聚合雙技能，即可應對靜態/動態、簡單/複雜各類行列轉換需求。