行轉列 (PIVOT) 與列轉行 (UNPIVOT) 是報表開發核心技能。本文以學生成績表為例,系統講解**靜態/動態 PIVOT**、**UNPIVOT 逆向操作**、**CASE 聚合兼容舊版**、**動態 SQL 參數化**四大技術路線,附完整可執行代碼。
CREATE TABLE #Score (Name NVARCHAR(10), Subject NVARCHAR(10), Result INT);
INSERT #Score VALUES
(N'張三',N'語文',73),(N'張三',N'數學',83),(N'張三',N'物理',93),
(N'李四',N'語文',74),(N'李四',N'數學',84),(N'李四',N'物理',94);
SELECT Name, [語文], [數學], [物理]
FROM #Score
PIVOT (MAX(Result) FOR Subject IN ([語文],[數學],[物理])) AS p;
| Name | 語文 | 數學 | 物理 |
|---|---|---|---|
| 張三 | 73 | 83 | 93 |
| 李四 | 74 | 84 | 94 |
DECLARE @Cols NVARCHAR(MAX), @SQL NVARCHAR(MAX);
SELECT @Cols = STRING_AGG(QUOTENAME(Subject), ',') -- 2017+
FROM (SELECT DISTINCT Subject FROM #Score) d;
SET @SQL = N'SELECT Name, ' + @Cols + N'
FROM #Score
PIVOT (MAX(Result) FOR Subject IN (' + @Cols + N')) p;';
EXEC sp_executesql @SQL;
2016 及之前用 `STUFF + FOR XML PATH` 拼接 `@Cols`。
SELECT
Name,
MAX(CASE WHEN Subject = N'語文' THEN Result END) AS [語文],
MAX(CASE WHEN Subject = N'數學' THEN Result END) AS [數學],
MAX(CASE WHEN Subject = N'物理' THEN Result END) AS [物理]
FROM #Score
GROUP BY Name;
動態版同理拼接 `CASE` 片段,**無需動態執行**,優化器可產生穩定計畫,推薦中小型報表優先使用。
CREATE TABLE #Wide (Name NVARCHAR(10), 語文 INT, 數學 INT, 物理 INT);
INSERT #Wide VALUES (N'張三',73,83,93),(N'李四',74,84,94);
SELECT Name, Subject, Score
FROM #Wide
UNPIVOT (Score FOR Subject IN ([語文],[數學],[物理])) AS u;
| Name | Subject | Score |
|---|---|---|
| 張三 | 語文 | 73 |
| 張三 | 數學 | 83 |
| 張三 | 物理 | 93 |
| 李四 | 語文 | 74 |
| 李四 | 數學 | 84 |
| 李四 | 物理 | 94 |
DECLARE @Cols NVARCHAR(MAX), @SQL NVARCHAR(MAX);
SELECT @Cols = STRING_AGG(QUOTENAME(name), ',')
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID('tempdb..#Wide') AND name NOT IN ('Name');
SET @SQL = N'SELECT Name, Subject, Score FROM #Wide UNPIVOT (Score FOR Subject IN (' + @Cols + N')) u;';
EXEC sp_executesql @SQL;
-- 將聚合類型納入列名
SELECT Name,
[語文_MAX], [語文_MIN], [語文_AVG],
[數學_MAX], [數學_MIN], [數學_AVG]
FROM (
SELECT Name, Subject + '_' + AggType AS Col, Val
FROM #Score
CROSS APPLY (VALUES
('MAX', MAX(Result) OVER (PARTITION BY Name, Subject)),
('MIN', MIN(Result) OVER (PARTITION BY Name, Subject)),
('AVG', AVG(Result*1.0) OVER (PARTITION BY Name, Subject))
) ca(AggType, Val)
) src
PIVOT (MAX(Val) FOR Col IN ([語文_MAX],[語文_MIN],[語文_AVG],[數學_MAX],[數學_MIN],[數學_AVG])) p;
| 場景 | 推薦方案 | 理由 |
|---|---|---|
| 固定列、高頻呼叫 | 靜態 PIVOT / CASE 聚合 | 計畫重用、無動態 SQL 開銷 |
| 動態列、報表引擎 | 動態 PIVOT (STRING_AGG/STUFF) | 列隨資料自適應 |
| 超大資料集 (>100萬行) | 預聚合 + 靜態 PIVOT | 縮小視窗輸入行數 |
| 需輸出多種聚合 | CROSS APPLY 構造多行 → PIVOT | 單次掃描完成多指標 |