SQL Server 行列轉換完整指南：PIVOT/UNPIVOT 與動態 SQL 實作

行列轉換是報表開發常見需求。本文示範靜態/動態 PIVOT（行轉列）、UNPIVOT（列轉行）及傳統 CASE 聚合法，並提供參數化動態腳本可直接套用。

範例資料：學生成績表

CREATE TABLE #Scores (Name VARCHAR(10), Subject VARCHAR(10), Score INT);
INSERT INTO #Scores VALUES
('張三','語文',73), ('張三','數學',83), ('張三','物理',93),
('李四','語文',74), ('李四','數學',84), ('李四','物理',94);

一、行轉列：長表 → 寬表

方法 1：傳統 CASE 聚合（全版本相容）

SELECT Name,
    SUM(CASE WHEN Subject = '語文' THEN Score END) AS 語文,
    SUM(CASE WHEN Subject = '數學' THEN Score END) AS 數學,
    SUM(CASE WHEN Subject = '物理' THEN Score END) AS 物理
FROM #Scores GROUP BY Name;

方法 2：靜態 PIVOT（SQL 2005+，語法簡潔）

SELECT * FROM (
    SELECT Name, Subject, Score FROM #Scores
) AS Src
PIVOT (SUM(Score) FOR Subject IN ([語文],[數學],[物理])) AS Pvt
ORDER BY Name;

方法 3：動態 PIVOT（適用欄位不固定）

DECLARE @Cols NVARCHAR(MAX), @SQL NVARCHAR(MAX);
SELECT @Cols = STRING_AGG(QUOTENAME(Subject), ',') FROM (SELECT DISTINCT Subject FROM #Scores) AS T;
-- SQL 2017+ 用 STRING_AGG，舊版用 STUFF + FOR XML PATH

SET @SQL = N'
SELECT * FROM (
    SELECT Name, Subject, Score FROM #Scores
) AS Src
PIVOT (SUM(Score) FOR Subject IN (' + @Cols + ')) AS Pvt
ORDER BY Name;';
EXEC sp_executesql @SQL;

方法 4：參數化動態 PIVOT 通用腳本（可直接套用專案）

DECLARE @TableName SYSNAME = 'Scores',
        @GroupCol SYSNAME = 'Name',
        @PivotCol SYSNAME = 'Subject',
        @ValueCol SYSNAME = 'Score',
        @WhereClause NVARCHAR(MAX) = ''; -- 可加 WHERE 條件

DECLARE @Cols NVARCHAR(MAX), @SQL NVARCHAR(MAX);

-- 1. 取得動態欄位清單
SET @SQL = N'SELECT @ColsOut = STRING_AGG(QUOTENAME(' + QUOTENAME(@PivotCol) + '), '','') FROM ' + QUOTENAME(@TableName) + @WhereClause;
EXEC sp_executesql @SQL, N'@ColsOut NVARCHAR(MAX) OUTPUT', @ColsOut = @Cols OUTPUT;

-- 2. 組裝並執行 PIVOT
SET @SQL = N'
SELECT * FROM (
    SELECT ' + QUOTENAME(@GroupCol) + ', ' + QUOTENAME(@PivotCol) + ', ' + QUOTENAME(@ValueCol) + '
    FROM ' + QUOTENAME(@TableName) + @WhereClause + '
) AS Src
PIVOT (SUM(' + QUOTENAME(@ValueCol) + ') FOR ' + QUOTENAME(@PivotCol) + ' IN (' + @Cols + ')) AS Pvt
ORDER BY ' + QUOTENAME(@GroupCol) + ';';
EXEC sp_executesql @SQL;

二、列轉行：寬表 → 長表

CREATE TABLE #Wide (Name VARCHAR(10), 語文 INT, 數學 INT, 物理 INT);
INSERT INTO #Wide VALUES ('張三',73,83,93), ('李四',74,84,94);

SELECT Name, '語文' AS Subject, 語文 AS Score FROM #Wide UNION ALL
SELECT Name, '數學', 數學 FROM #Wide UNION ALL
SELECT Name, '物理', 物理 FROM #Wide
ORDER BY Name;

SQL 2005+ 使用 UNPIVOT

SELECT Name, Subject, Score
FROM #Wide
UNPIVOT (Score FOR Subject IN ([語文],[數學],[物理])) AS Unpvt;

動態 UNPIVOT 參數化腳本

DECLARE @TableName SYSNAME = 'Wide', @IdCol SYSNAME = 'Name', @ValueCols NVARCHAR(MAX) = '語文,數學,物理';
DECLARE @SQL NVARCHAR(MAX) = N'
SELECT ' + QUOTENAME(@IdCol) + ', Subject, Value
FROM ' + QUOTENAME(@TableName) + '
UNPIVOT (Value FOR Subject IN (' + @ValueCols + ')) AS U;';
EXEC sp_executesql @SQL;

效能與選型建議

掌握 PIVOT/UNPIVOT 與動態 SQL 組合，即可靈活應對各類行列轉換需求，提升報表開發效率。