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

SQL Server 2005 資料行列轉換完全攻略：PIVOT/UNPIVOT 與動態 SQL 實戰

編輯：SQL Server 2005

一、行轉列（PIVOT） - 課程成績橫向展示

場景

縱向儲存：每列一筆 (學生, 科目, 分數)。需橫向報表：每列一位學生，科目變欄位。

建表與測試資料


CREATE TABLE RowTest (
    [Name] NVARCHAR(10),
    [Course] NVARCHAR(10),
    [Record] INT
);
INSERT INTO RowTest VALUES
(N'張三',N'語文',91),(N'張三',N'數學',92),(N'張三',N'英語',93),
(N'張三',N'生物',94),(N'張三',N'物理',95),(N'張三',N'化學',96),
(N'李四',N'語文',81),(N'李四',N'數學',82),(N'李四',N'英語',83),
(N'李四',N'生物',84),(N'李四',N'物理',85),(N'李四',N'化學',86),
(N'小生',N'語文',71),(N'小生',N'數學',72),(N'小生',N'英語',73),
(N'小生',N'生物',74),(N'小生',N'物理',75),(N'小生',N'化學',76);

靜態 PIVOT（科目固定）


SELECT [Name], [語文], [數學], [英語], [生物], [物理], [化學]
FROM RowTest
PIVOT (SUM([Record]) FOR [Course] IN ([語文],[數學],[英語],[生物],[物理],[化學])) AS PVT;

動態 PIVOT（科目不固定，自動展開）


DECLARE @sql NVARCHAR(MAX) = N'SELECT [Name]';
SELECT @sql += N', ISNULL(MAX(CASE WHEN [Course] = ' + QUOTENAME([Course], '''') + N' THEN [Record] END), 0) AS ' + QUOTENAME([Course])
FROM (SELECT DISTINCT [Course] FROM RowTest) AS C;
SET @sql += N' FROM RowTest GROUP BY [Name] ORDER BY [Name]';
PRINT @sql;  -- 除錯用
EXEC sp_executesql @sql;

原理：先查詢所有唯一科目，動態拼裝 CASE WHEN ... THEN ... END 聚合運算式，再 GROUP BY Name 合併同一學生的多行。ISNULL(...,0) 處理缺考顯示 0 分。

擴充測試

新增重複科目記錄 → 需保留 MAX 或 SUM 聚合，否則報錯「子查詢傳回多個值」。
新增新科目（如「計算機」） → 動態 SQL 自動多出一欄，其他學生該欄顯示 0。

二、列轉行（UNPIVOT） - 橫向表轉縱向明細

場景

橫向表 CoulumTest(Name, 語文, 數學, 英語) 需轉為縱向 (Name, Course, Score) 便於統計分析。

建表與資料


CREATE TABLE CoulumTest (
    Name NVARCHAR(10), 語文 INT, 數學 INT, 英語 INT
);
INSERT INTO CoulumTest VALUES (N'張三',90,91,92), (N'李四',80,81,82);

創新單一查詢法（利用 sys.columns 取得欄位清單）


SELECT CT.Name,
       Col.name AS 課程,
       CASE Col.name
           WHEN N'語文' THEN CT.語文
           WHEN N'數學' THEN CT.數學
           WHEN N'英語' THEN CT.英語
       END AS 分數
FROM CoulumTest CT
LEFT JOIN (
    SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('CoulumTest')
) Col ON Col.name <> 'Name';

優點：無需 UNION ALL 手寫每欄，利用系統目錄自動獲取需轉換的欄位清單。
缺點：仍需在 CASE WHEN 硬編碼欄位對應值，無法完全通用。

標準 UNPIVOT 寫法（完全通用）


SELECT Name, Course, Score
FROM CoulumTest
UNPIVOT (Score FOR Course IN ([語文], [數學], [英語])) AS UP;

若欄位動態，可結合動態 SQL 組裝 IN 清單。

三、PIVOT + UNPIVOT 往復轉換演示


-- 縱向 → 橫向 → 再轉回縱向
SELECT Name, Course, Score
FROM (
    SELECT [Name], [語文], [數學], [英語], [生物], [物理], [化學]
    FROM RowTest
    PIVOT (SUM(Record) FOR Course IN ([語文],[數學],[英語],[生物],[物理],[化學])) AS PVT
) P
UNPIVOT (Score FOR Course IN ([語文],[數學],[英語],[生物],[物理],[化學])) AS UNPVT;

四、效能與適用性建議

方法	適用場景	效能	維護性
靜態 PIVOT/UNPIVOT	欄位固定、報表固定	最佳（查詢計劃可重用）	高
動態 SQL + CASE 聚合	行轉列、欄位不固定	良好（單次掃描）	中（需動態 SQL）
動態 SQL + UNPIVOT	列轉行、欄位不固定	良好	中
應用層轉換	複雜邏輯、多格式輸出	移至應用層	最高