SQL Server 2005 PIVOT 運算子實作交叉表報表：靜態與動態 SQL 完整範例

需求：列轉行產生交叉表

原始資料：

日期        時間  售貨金額
2006-01-02  早上   50
2006-01-02  中午   20
2006-01-02  晚上   30
2006-01-02  零晨   40
2006-01-03  早上   40
...

目標輸出：

日期        早上  中午  晚上  零晨  金額小計
2006-01-02  50    20    30    40    140
2006-01-03  40    60    50    50    200
...

靜態 PIVOT 寫法

-- 建立測試資料
CREATE TABLE T (日期 datetime, 時間 varchar(20), 售貨金額 int);
INSERT INTO T VALUES
('2006-01-02','早上',50), ('2006-01-02','中午',20), ('2006-01-02','晚上',30), ('2006-01-02','零晨',40),
('2006-01-03','早上',40), ('2006-01-03','中午',60), ('2006-01-03','晚上',50), ('2006-01-03','零晨',50),
('2006-01-04','早上',80), ('2006-01-04','中午',60), ('2006-01-04','晚上',20), ('2006-01-04','零晨',40);

-- PIVOT 查詢
SELECT 日期,
       [早上], [中午], [晚上], [零晨],
       (SELECT SUM(售貨金額) FROM T WHERE 日期 = PT.日期) AS 金額小計
FROM T AS TAB
PIVOT ( MAX(售貨金額) FOR 時間 IN ([早上],[中午],[晚上],[零晨]) ) AS PT;

DROP TABLE T;

關鍵點：

• MAX(售貨金額) 為彙總函數（每組僅一列，MAX/MIN/SUM 皆可）。
• FOR 時間 IN (...) 明確列出目標欄位，順序決定輸出欄序。
• 子查詢計算小計，或可用 SUM(...) OVER (PARTITION BY 日期)（SQL 2012+）。

動態 PIVOT（欄位不固定）

DECLARE @cols VARCHAR(MAX) = '';
SELECT @cols += ',[' + 時間 + ']' FROM T GROUP BY 時間;
SET @cols = STUFF(@cols, 1, 1, '');  -- 移除前導逗號

DECLARE @sql NVARCHAR(MAX) = N'
SELECT 日期, ' + @cols + ',
       (SELECT SUM(售貨金額) FROM T WHERE 日期 = PT.日期) AS 金額小計
FROM T AS TAB
PIVOT ( MAX(售貨金額) FOR 時間 IN (' + @cols + ') ) AS PT;';

EXEC sp_executesql @sql;

動態原理：先查詢ISTINCT 時間值拼湊 IN 清單，再組裝完整 SQL 字串動態執行。適用於「時間區間不固定、新增類別無需改程式」場景。

進階技巧

• 多欄位 PIVOT：使用 CROSS APPLY 或多層 PIVOT。
• NULL 填補：ISNULL([早上], 0)。
• 總計列：在外層 UNION ALL 彙總列，或用 GROUPING SETS（SQL 2008+）。
• 效能：PIVOT 內部實作為 CASE 聚合，大量資料時確保 時間 欄位有索引。

總結

SQL Server 2005 引入的 PIVOT 運算子以宣告式語法優雅解決交叉表需求。靜態版本適用固定欄位報表；動態版本配合字串拼裝應對彈性欄位。掌握 PIVOT 與 UNPIVOT，可大幅簡化報表查詢邏輯，提升可維護性。