動態 SQL 實現交叉表（行轉列）查詢：按類別分欄統計

需求場景

原始表 T(Class, CallDate, CallCount)，需轉為：
CallDate | CallCount1 | CallCount2 | CallCount3
無記錄的日期-類別組合顯示 0。

動態 SQL 解法（SQL Server）

-- 1. 建立測試資料
CREATE TABLE T (Class VARCHAR(2), CallDate DATETIME, CallCount INT);
INSERT INTO T VALUES
('1','2005-08-08',40),('1','2005-08-07',6),
('2','2005-08-08',77),
('3','2005-08-09',33),('3','2005-08-08',9),('3','2005-08-07',21);

-- 2. 動態產生 SELECT 列清單
DECLARE @sql NVARCHAR(MAX) = N'SELECT CallDate ';
SELECT @sql += N', [CallCount' + Class + N'] = SUM(CASE WHEN Class = ''' + Class + N''' THEN CallCount ELSE 0 END)'
FROM T
GROUP BY Class;

-- 3. 完成完整查詢
SET @sql += N' FROM T GROUP BY CallDate ORDER BY CallDate DESC';
EXEC sp_executesql @sql;

-- 4. 清理
DROP TABLE T;

輸出結果

CallDate                 CallCount1 CallCount2 CallCount3
-----------------------  ---------- ---------- ----------
2005-08-09 00:00:00.000  0          0          33
2005-08-08 00:00:00.000  40         77         9
2005-08-07 00:00:00.000  6          0          21

進階：PIVOT 寫法（SQL Server 2005+）

SELECT CallDate,
       ISNULL([1],0) AS CallCount1,
       ISNULL([2],0) AS CallCount2,
       ISNULL([3],0) AS CallCount3
FROM (
    SELECT Class, CallDate, CallCount FROM T
) src
PIVOT (
    SUM(CallCount) FOR Class IN ([1],[2],[3])
) pvt
ORDER BY CallDate DESC;

靜態 PIVOT 需預知類別值；動態 PIVOT 仍需拼接列清單字串。

跨資料庫通用做法

• Oracle：PIVOT / CASE ... SUM + XML 動態。
• PostgreSQL：crosstab() (tablefunc 擴充) 或 FILTER 聚合。
• MySQL：CASE WHEN ... THEN ... ELSE 0 END 聚合 + PREPARE 動態執行。

效能提示

1. 源表建立 INDEX (CallDate, Class) INCLUDE (CallCount) 覆蓋索引。
2. 動態 SQL 在應用層快取產生的列清單，避免每次重組。
3. 類別值固定時優先用靜態 PIVOT/CASE，計畫可重用。