萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 行列轉換完全指南:動態 PIVOT/UNPIVOT、CASE 聚合與 XML 路徑實戰

行轉列 (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);

1. 靜態 PIVOT (SQL Server 2005+)

SELECT Name, [語文], [數學], [物理]
FROM #Score
PIVOT (MAX(Result) FOR Subject IN ([語文],[數學],[物理])) AS p;
Name語文數學物理
張三738393
李四748494

2. 動態 PIVOT (列名未知/可變)

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`。

3. CASE 聚合法 (全版本通用、無動態 SQL、效能相近)

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` 片段,**無需動態執行**,優化器可產生穩定計畫,推薦中小型報表優先使用。

4. 逆向操作:UNPIVOT (列式 → 行式)

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;
NameSubjectScore
張三語文73
張三數學83
張三物理93
李四語文74
李四數學84
李四物理94

5. 動態 UNPIVOT (列名未知)

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;

6. 進階:多值聚合 PIVOT (同時輸出 MAX/MIN/AVG/COUNT)

-- 將聚合類型納入列名
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/UNPIVOT 雙向轉換、動態列構造、CASE 聚合替代方案,即可應對 99% 報表行列轉換需求。

copyright © 萬盛學電腦網 all rights reserved
場景推薦方案理由
固定列、高頻呼叫靜態 PIVOT / CASE 聚合計畫重用、無動態 SQL 開銷
動態列、報表引擎動態 PIVOT (STRING_AGG/STUFF)列隨資料自適應
超大資料集 (>100萬行)預聚合 + 靜態 PIVOT縮小視窗輸入行數
需輸出多種聚合CROSS APPLY 構造多行 → PIVOT單次掃描完成多指標