交叉表（行轉列）是報表開發常見需求。SQL Server 2005 前需動態 SQL 拼接 CASE 陳述式，2005+ 原生 PIVOT 語法雖簡化靜態轉換，但動態欄位仍需動態 SQL。本文提供極簡動態 PIVOT 範本，適用於欄位不固定的報表場景。
SET NOCOUNT ON
CREATE TABLE #test (
model VARCHAR(20),
dt INT,
qty INT
)
INSERT INTO #test VALUES ('a', 8, 10), ('a', 10, 50)
INSERT INTO #test VALUES ('b', 8, 100), ('b', 9, 200), ('b', 10, 100)
INSERT INTO #test VALUES ('c', 10, 200)
INSERT INTO #test VALUES ('d', 10, 300)
INSERT INTO #test VALUES ('e', 11, 250), ('e', 12, 100)
INSERT INTO #test VALUES ('f', 12, 150)
GO
DECLARE @sql NVARCHAR(MAX) = N'SELECT model, '
-- 1. 動態產生聚合欄位清單（依日期排序、去重）
SELECT @sql += N'SUM(CASE WHEN dt = ' + CAST(dt AS NVARCHAR(10))
+ N' THEN qty ELSE 0 END) AS [' + CAST(dt AS NVARCHAR(10)) + N'],'
FROM (
SELECT DISTINCT dt FROM #test
) AS d
ORDER BY dt
-- 2. 移除尾端逗號，補完 FROM/GROUP BY
SET @sql = LEFT(@sql, LEN(@sql) - 1) + N' FROM #test GROUP BY model'
-- 3. 執行
EXEC sp_executesql @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 |
DECLARE
@aggCol SYSNAME = 'qty', -- 彙總欄位
@pivotCol SYSNAME = 'dt', -- 樞紐欄位
@rowCol SYSNAME = 'model', -- 列欄位
@tableName SYSNAME = '#test',
@sql NVARCHAR(MAX)
-- 產生欄位清單（含排序、去重、別名格式化）
DECLARE @cols NVARCHAR(MAX) = (
SELECT STRING_AGG( -- SQL 2017+，舊版用 STUFF+FOR XML PATH
QUOTENAME(CAST(pivot_val AS NVARCHAR(50))) + ' = ' +
'SUM(CASE WHEN ' + QUOTENAME(@pivotCol) + ' = ' + CAST(pivot_val AS NVARCHAR(50)) +
' THEN ' + QUOTENAME(@aggCol) + ' ELSE 0 END)',
', '
) WITHIN GROUP (ORDER BY pivot_val)
FROM (SELECT DISTINCT CAST(dt AS NVARCHAR(50)) AS pivot_val FROM #test) d
)
SET @sql = N'SELECT ' + QUOTENAME(@rowCol) + N', ' + @cols +
N' FROM ' + QUOTENAME(@tableName) +
N' GROUP BY ' + QUOTENAME(@rowCol) +
N' ORDER BY ' + QUOTENAME(@rowCol)
EXEC sp_executesql @sql
SELECT model, [8], [9], [10], [11], [12]
FROM (
SELECT model, dt, qty FROM #test
) AS src
PIVOT (
SUM(qty) FOR dt IN ([8], [9], [10], [11], [12])
) AS pvt
ORDER BY model
-- 假設已有寬表 #pivot_result
SELECT model, dt, qty
FROM #pivot_result
UNPIVOT (
qty FOR dt IN ([8], [9], [10], [11], [12])
) AS unpvt
| 議題 | 建議 |
|---|---|
| SQL 注入 | 欄位名來自系統表/參數，非使用者直接輸入；必要時用 QUOTENAME 防注入 |
| 執行計畫重用 | 動態 SQL 每次重編譯；高頻呼叫考慮快取結果或改用應用層轉換 |
| NULL 處理 | SUM 忽略 NULL，CASE ELSE 0 確保無資料顯示 0 而非 NULL |
| 大量欄位 | 欄位 > 100 時動態 SQL 字串極長，考慮應用層處理或分頁顯示 |
動態 PIVOT 本質是執行階段產生靜態 PIVOT SQL。掌握欄位清單動態組裝、QUOTENAME 防注入、sp_executesql 參數化執行三要素，即可應對多數交叉表報表需求。SQL 2017+ 的 STRING_AGG 讓欄位組裝更優雅；舊版可用 STUFF + FOR XML PATH 達成同樣效果。
