SQL Server 動態交叉表 (Dynamic Pivot) 進階範例：FOR XML PATH 動態建構欄位清單

承接上一篇游標版本，本文展示更高效的 FOR XML PATH 技巧動態產生欄位清單，避免游標逐列迴圈效能開銷，適合 SQL Server 2005 以上版本。範例包含完整建立測試資料、動態 PIVOT 查詢、清理環境腳本，並附上預期結果對照。

一、 建立測試環境與資料

SET NOCOUNT ON;
CREATE TABLE test (model VARCHAR(20), [date] INT, qty INT);

INSERT INTO test SELECT 'a', 8, 10;
INSERT INTO test SELECT 'a', 10, 50;
INSERT INTO test SELECT 'b', 8, 100;
INSERT INTO test SELECT 'b', 9, 200;
INSERT INTO test SELECT 'b', 10, 100;
INSERT INTO test SELECT 'c', 10, 200;
INSERT INTO test SELECT 'd', 10, 300;
INSERT INTO test SELECT 'e', 11, 250;
INSERT INTO test SELECT 'e', 12, 100;
INSERT INTO test SELECT 'f', 12, 150;
GO

二、 動態 PIVOT 核心腳本 (FOR XML PATH)

DECLARE @sql VARCHAR(8000);
SET @sql = 'SELECT model,';

-- 關鍵：利用 FOR XML PATH('') 將不重複的 date 值串聯成 "SUM(CASE...) AS [date]," 格式
SELECT @sql = @sql + 'SUM(CASE WHEN [date] = ''' + CAST([date] AS VARCHAR(10)) + ''' THEN qty ELSE 0 END) [' + CAST([date] AS VARCHAR(10)) + '],'
FROM (
    SELECT DISTINCT TOP 100 PERCENT [date]
    FROM test
    ORDER BY [date]
) a;

-- 移除最後一個逗號，補上 FROM + GROUP BY
SET @sql = LEFT(@sql, LEN(@sql) - 1) + ' FROM test GROUP BY model';

-- 執行動態 SQL
EXEC(@sql);

三、 執行結果對照

model  8    9    10   11   12
-----  ---  ---  ---  ---  ---
a      10   0    50   0    0
b      100  200  100  0    0
c      0    0    200  0    0
d      0    0    300  0    0
e      0    0    0    250  100
f      0    0    0    0    150

四、 清理測試環境

DROP TABLE test;
SET NOCOUNT OFF;

五、 技術要點解析

1. FOR XML PATH('')：將多列結果串聯成單一字串，是 SQL Server 字串聚合的經典技巧 (2017+ 可用 STRING_AGG 取代)。
2. TOP 100 PERCENT + ORDER BY：確保動態欄位順序穩定 (依日期遞增)，否則 PIVOT 欄位順序不固定。
3. CAST([date] AS VARCHAR(10))：數值型欄位需轉字串才能串聯進動態 SQL，並作為別名使用。
4. 動態 SQL 長度限制：VARCHAR(8000) / NVARCHAR(MAX) 需視欄位數量調整，超長需分批處理。

六、 進階擴充：支援多統計欄位與總計列

-- 同時產生 SUM、AVG 兩種統計
SELECT @sql = @sql + 'SUM(CASE WHEN [date] = ''' + CAST([date] AS VARCHAR(10)) + ''' THEN qty ELSE 0 END) AS [Sum_' + CAST([date] AS VARCHAR(10)) + '],'
             + 'AVG(CASE WHEN [date] = ''' + CAST([date] AS VARCHAR(10)) + ''' THEN qty * 1.0 ELSE NULL END) AS [Avg_' + CAST([date] AS VARCHAR(10)) + '],'
FROM ...

此法靈活度極高，可輕鬆擴充為多維度、多指標的動態報表引擎核心。