您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> SQL Server 2005

SQL Server 2005 PIVOT/UNPIVOT 行列轉換實戰：從儲存模型到報表呈現

編輯：SQL Server 2005

核心場景

學生選課成績系統：課程數量不固定，資料表採用**縱向儲存** (Student, SubjectID, Score) 彈性擴充；報表需**橫向呈現** (Student, Subject1, Subject2, ...) 方便閱讀。PIVOT/UNPIVOT 正是解決此「儲存與呈現不一致」的關鍵工具。

建表與測試資料


CREATE TABLE Table_1 (
    [name] VARCHAR(50) NOT NULL,
    [score] REAL NOT NULL,
    [subject_id] INT NOT NULL
);
INSERT INTO Table_1 VALUES
('張三',90,1),('張三',80,2),('張三',70,3),
('王五',50,1),('王五',40,2),
('李四',60,1);

一、PIVOT：縱向轉橫向 (行轉列)


SELECT [name], [1], [2], [3]
FROM Table_1
PIVOT (SUM(score) FOR subject_id IN ([1],[2],[3])) AS pvt;

結果：

name	1	2	3
張三	90	80	70
王五	50	40	NULL
李四	60	NULL	NULL

關鍵點：
① 聚合函數 (SUM/MAX/AVG) 必須指定，即使每組僅一筆。
② IN 清單需硬編碼所有可能的 subject_id 值。
③ 缺失組合顯示 NULL，可用 ISNULL 替換為 0。

二、UNPIVOT：橫向轉縱向 (列轉行)

假設已有橫向表（或由 PIVOT 產生），需還原為縱向明細：


SELECT [name], [subject_id], [score]
FROM (
    SELECT [name], [1], [2], [3]
    FROM Table_1
    PIVOT (SUM(score) FOR subject_id IN ([1],[2],[3])) AS pvt
) p
UNPIVOT (score FOR subject_id IN ([1],[2],[3])) AS unpvt;

結果：還原為原始縱向三欄格式，NULL 值預設被過濾（不產生行）。

三、動態 PIVOT：應對不固定課程


DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(subject_id), ',')  -- SQL 2017+；2005 需用 FOR XML PATH
FROM (SELECT DISTINCT subject_id FROM Table_1) t;

SET @sql = N'SELECT [name], ' + @cols + N'
FROM Table_1
PIVOT (SUM(score) FOR subject_id IN (' + @cols + N')) AS pvt';
EXEC sp_executesql @sql;

SQL Server 2005 取代 STRING_AGG 寫法：


SELECT @cols = COALESCE(@cols + ',', '') + QUOTENAME(subject_id)
FROM (SELECT DISTINCT subject_id FROM Table_1) t;

四、效能與限制

五、替代方案對比

項目	說明
索引建議	來源表建立 `(subject_id, name) INCLUDE (score)` 或 `(name, subject_id) INCLUDE (score)` 覆蓋索引，支援 PIVOT 掃描與聚合。
NULL 處理	PIVOT 結果缺失組合為 NULL；UNPIVOT 預設過濾 NULL，如需保留需用 `CASE` 手寫轉換。
資料型別	`IN` 清單值必須與轉換欄位型別一致（隱含轉換可能失敗）。
最大欄位數	受限於 SELECT 清單上限 (4096)，動態 PIVOT 欄位過多會報錯。
查詢計劃重用	靜態 PIVOT 計劃可重用；動態 SQL 每次重新編譯，高併發下建議加 `OPTION (RECOMPILE)` 或參數化。

方案	優點	缺點	適用場景
PIVOT/UNPIVOT	T-SQL 原生、可嵌入 View/SP、效能良好	靜態欄位硬編碼、動態需動態 SQL	報表、匯出、中間層轉換
應用層轉換	彈性最高、支援複雜格式 (Excel 合併儲存格、跨頁)	資料傳輸量大、雙重開發維護	複雜報表、多格式匯出
SSRS/BI 工具	內建矩陣報表、參數化、排程訂閱	需部署 Reporting Services	企業級報表平台