資料樞紐 (Pivot) 將「長表」 (行導向) 轉為「寬表」 (列導向),是報表與 BI 分析的核心需求。SQL Server 提供靜態 `PIVOT` 運算子與動態 SQL 兩種路徑,本文從基礎語法進階至全自動動態列生成方案。
CREATE TABLE Sales (SalesPerson NVARCHAR(10), Book NVARCHAR(20), Qty INT);
INSERT INTO Sales VALUES
('小王','Excel教材',10), ('小李','Excel教材',15), ('小王','Word教材',8),
('小李','Excel教材',7), ('小王','Excel教材',9), ('小李','Excel教材',2),
('小王','Word教材',3), ('小李','Excel教材',5);
-- 靜態 PIVOT (SQL Server 2005+)
SELECT SalesPerson,
ISNULL([Excel教材], 0) AS [Excel教材],
ISNULL([Word教材], 0) AS [Word教材],
ISNULL([Excel教材],0) + ISNULL([Word教材],0) AS [總計]
FROM (
SELECT SalesPerson, Book, Qty FROM Sales
) src
PIVOT (
SUM(Qty) FOR Book IN ([Excel教材], [Word教材])
) pvt;
結果:
SalesPerson Excel教材 Word教材 總計
小王 29 11 40
小李 29 0 29
限制:`IN` 列表必須硬編碼,新增書籍需修改代碼。
利用 `QUOTENAME` 防注入、`FOR XML PATH` 串聯列清單、執行動態 SQL。
DECLARE @cols NVARCHAR(MAX) = N'';
DECLARE @sql NVARCHAR(MAX) = N'';
-- 1. 動態獲取所有 distinct Book 值,構建 [列名] 清單
SELECT @cols += N',' + QUOTENAME(Book)
FROM (SELECT DISTINCT Book FROM Sales) AS b
ORDER BY Book; -- 保證列順序穩定
-- 移除前導逗號
SET @cols = STUFF(@cols, 1, 1, N'');
-- 2. 構建完整查詢
SET @sql = N'
SELECT SalesPerson, ' + @cols + N',
(' + REPLACE(@cols, N'],[', N']+[') + N') AS [總計]
FROM (
SELECT SalesPerson, Book, Qty FROM Sales
) src
PIVOT (
SUM(Qty) FOR Book IN (' + @cols + N')
) pvt;';
-- 3. 執行
EXEC sp_executesql @sql;
QUOTENAME(Book):自動加 `[ ]` 雙引號並轉義內部 `]`,防 SQL 注入,處理含空格/特殊字元列名。STUFF(@cols, 1, 1, N''):移除首個逗號。REPLACE(@cols, N'],[', N']+['):巧妙生成總計運算式 (`[Excel教材]+[Word教材]...`)。sp_executesql:優於 `EXEC()`,支援參數化、計畫複用。單次 `PIVOT` 僅支援一個聚合函數。解法:多次 PIVOT 串聯 或 條件聚合 (CASE WHEN) (效能通常更好)。
SELECT SalesPerson,
SUM(CASE WHEN Book = N'Excel教材' THEN Qty END) AS [Excel教材_銷量],
COUNT(CASE WHEN Book = N'Excel教材' THEN 1 END) AS [Excel教材_筆數],
SUM(CASE WHEN Book = N'Word教材' THEN Qty END) AS [Word教材_銷量],
COUNT(CASE WHEN Book = N'Word教材' THEN 1 END) AS [Word教材_筆數]
FROM Sales
GROUP BY SalesPerson;
DECLARE @cols NVARCHAR(MAX) = N'', @agg NVARCHAR(MAX) = N'';
SELECT @cols += N',' + QUOTENAME(Book),
@agg += N',SUM(CASE WHEN Book=' + QUOTENAME(Book, N'''') + N' THEN Qty END) AS ' + QUOTENAME(Book + N'_銷量')
+ N',COUNT(CASE WHEN Book=' + QUOTENAME(Book, N'''') + N' THEN 1 END) AS ' + QUOTENAME(Book + N'_筆數')
FROM (SELECT DISTINCT Book FROM Sales) b;
SET @cols = STUFF(@cols, 1, 1, N'');
SET @agg = STUFF(@agg, 1, 1, N'');
EXEC sp_executesql @sql = N'SELECT SalesPerson, ' + @agg + N' FROM Sales GROUP BY SalesPerson;';
靜態 `PIVOT` 適合固定報表;動態 SQL + `QUOTENAME` + `FOR XML PATH`/`STRING_AGG` (2017+) 是實現「任意列樞紐」的標準模式;**條件聚合** 則是效能與彈性的最佳平衡點。根據報表變動頻率、資料量、維護成本三角權衡選擇方案。