SQL Server 2005 PIVOT/UNPIVOT 完整實戰：行轉列、列轉行與動態列生成技巧

PIVOT / UNPIVOT 核心概念

• PIVOT： 將「長表」 (行導向) 轉為「寬表」 (列導向) —— **行變列**，並可彙總 (SUM/COUNT/AVG 等)。
• UNPIVOT： 將「寬表」轉為「長表」 —— **列變行**，但**無法完美還原**已彙總的資料 (資訊損失)。
• 支援版本：**SQL Server 2005+**，資料庫相容等級需 **≥ 90**。

環境準備：相容等級檢查與調整

-- 檢查
SELECT compatibility_level FROM sys.databases WHERE name = 'YourDB';
-- 調整 (需獨佔或單一使用者模式)
ALTER DATABASE YourDB SET COMPATIBILITY_LEVEL = 100; -- SQL 2008
-- 或 90 (SQL 2005)

範例資料：季度銷售額

CREATE TABLE Sales (EmpID INT, EmpName NVARCHAR(20), Quarter INT, Amount INT);
INSERT INTO Sales VALUES
(1, N'Simon', 1, 1000), (1, N'Simon', 2, 2000), (1, N'Simon', 3, 3000), (1, N'Simon', 4, 4000),
(2, N'Meme',  1, 5000), (2, N'Meme',  2, 6000), (2, N'Meme',  3, 7000), (2, N'Meme',  4, 8000);

PIVOT：行轉列 (季度 → 列)

SELECT EmpID, EmpName,
       [1] AS Q1, [2] AS Q2, [3] AS Q3, [4] AS Q4
FROM Sales
PIVOT (
    SUM(Amount) FOR Quarter IN ([1], [2], [3], [4])
) AS Pvt;

結果

EmpID EmpName Q1    Q2    Q3    Q4
1     Simon   1000  2000  3000  4000
2     Meme    5000  6000  7000  8000

UNPIVOT：列轉行 (寬表 → 長表)

CREATE TABLE SalesWide (EmpID INT, EmpName NVARCHAR(20), Q1 INT, Q2 INT, Q3 INT, Q4 INT);
INSERT INTO SalesWide VALUES
(1, N'Simon', 1000, 2000, 4000, 5000),
(2, N'Meme',  3000, 3500, 4200, 5500);

SELECT EmpID, EmpName, Quarter, Amount
FROM SalesWide
UNPIVOT (
    Amount FOR Quarter IN (Q1, Q2, Q3, Q4)
) AS Unpvt;

結果

EmpID EmpName Quarter Amount
1     Simon   Q1      1000
1     Simon   Q2      2000
1     Simon   Q3      4000
1     Simon   Q4      5000
2     Meme    Q1      3000
...

進階技巧

1. 多欄位彙總 (銷售額 + 數量)

SELECT EmpID, EmpName,
       SUM(CASE WHEN Quarter=1 THEN Amount END) AS Q1_Amt,
       SUM(CASE WHEN Quarter=1 THEN Qty END)    AS Q1_Qty,
       ...
FROM Sales GROUP BY EmpID, EmpName;

PIVOT 單次僅支援一個彙總欄位，多欄位需多次 PIVOT 再 JOIN，或改用 CASE 聚合 (效能更好、可讀性高)。

2. 動態 PIVOT：列名不固定 (如月份、產品類別)

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- 1. 取得所有季度/月份/類別，生成列清單
SELECT @cols = STUFF((
    SELECT DISTINCT N'],[' + QUOTENAME(CAST(Quarter AS NVARCHAR(10)))
    FROM Sales
    ORDER BY N'],[' + QUOTENAME(CAST(Quarter AS NVARCHAR(10)))
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

-- 2. 建構動態 SQL
SET @sql = N'
SELECT EmpID, EmpName, ' + @cols + N'
FROM Sales
PIVOT (SUM(Amount) FOR Quarter IN (' + @cols + N')) AS Pvt;';

-- 3. 執行
EXEC sp_executesql @sql;

3. PIVOT + 動態 SQL 產生報表 (SSRS/Excel 整合)

• 將上述動態 SQL 封裝為預存程序 `usp_GetSalesPivot @Year INT`。
• SSRS 資料集呼叫該 SP，報表欄位自動適應回傳結構 (需設定 `Field` 為動態或使用 Matrix 資料區)。

效能與限制注意事項

PIVOT vs CASE 聚合：選型建議