SQL Server 2005+ 進階透視：PIVOT 行轉欄與 UNPIVOT 欄轉行完整語法與範例

PIVOT：行轉欄

語法架構

SELECT <非透視欄位>, [透視欄位值清單]
FROM (
    SELECT <非透視欄位>, <透視欄位>, <彙總欄位>
    FROM 來源表
) AS src
PIVOT (
    彙總函數(彙總欄位)
    FOR 透視欄位 IN ([值1], [值2], ...)
) AS pvt;

範例：學生成績行轉欄

-- 建立測試資料
CREATE TABLE #Score (Name NVARCHAR(10), Subject NVARCHAR(10), Score INT);
INSERT INTO #Score VALUES
(N'張三',N'語文',74), (N'張三',N'數學',83), (N'張三',N'物理',93),
(N'李四',N'語文',74), (N'李四',N'數學',84), (N'李四',N'物理',94);

-- PIVOT 查詢
SELECT Name, [語文], [數學], [物理]
FROM #Score
PIVOT (
    MAX(Score) FOR Subject IN ([語文], [數學], [物理])
) AS p;

結果

Name  語文  數學  物理
張三   74    83    93
李四   74    84    94

動態 PIVOT（欄位不固定）

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(Subject), ',')  -- SQL 2017+
FROM (SELECT DISTINCT Subject FROM #Score) t;

SET @sql = N'SELECT Name, ' + @cols + N'
FROM #Score
PIVOT (MAX(Score) FOR Subject IN (' + @cols + N')) AS p';
EXEC sp_executesql @sql;

SQL 2016 及以前可用 FOR XML PATH 串接欄位清單。

UNPIVOT：欄轉行

語法架構

SELECT <非透視欄位>, <值欄位名>, <欄位名欄位名>
FROM (
    SELECT <非透視欄位>, [欄1], [欄2], ...
    FROM 來源表
) AS src
UNPIVOT (
    <值欄位名> FOR <欄位名欄位名> IN ([欄1], [欄2], ...)
) AS unpvt;

範例：將上述 PIVOT 結果還原

SELECT Name, Score, Subject
FROM (
    SELECT Name, [語文], [數學], [物理]
    FROM #Score
    PIVOT (MAX(Score) FOR Subject IN ([語文], [數學], [物理])) AS p
) AS src
UNPIVOT (
    Score FOR Subject IN ([語文], [數學], [物理])
) AS u;

結果

Name  Score  Subject
張三   74    語文
張三   83    數學
張三   93    物理
李四   74    語文
李四   84    數學
李四   94    物理

SQL 2000 相容寫法：UNION ALL 模擬 UNPIVOT

SELECT Name, [語文] AS Score, N'語文' AS Subject FROM #PivotResult
UNION ALL
SELECT Name, [數學], N'數學' FROM #PivotResult
UNION ALL
SELECT Name, [物理], N'物理' FROM #PivotResult;

效能與限制提醒

• PIVOT/UNPIVOT 需要預知欄位值清單，動態欄位需動態 SQL。
• 彙總函數常用 MAX、SUM、COUNT；若同一群組有多列，結果取決於彙總邏輯。
• 大量資料建議先彙總再 PIVOT，減少中間結果集大小。
• UNPIVOT 會過濾掉 NULL 值列；若需保留 NULL，需用 CROSS APPLY VALUES 替代。