萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 動態樞紐分析 (PIVOT) 實戰:從靜態 CASE 到動態 SQL 自動生成列

資料樞紐 (Pivot) 將「長表」 (行導向) 轉為「寬表」 (列導向),是報表與 BI 分析的核心需求。SQL Server 提供靜態 `PIVOT` 運算子與動態 SQL 兩種路徑,本文從基礎語法進階至全自動動態列生成方案。

一、 樞紐基礎概念與靜態 PIVOT

範例資料:銷售記錄

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` 列表必須硬編碼,新增書籍需修改代碼。

二、 動態 SQL 實現自動列生成 (核心方案)

利用 `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;';

四、 效能與最佳實踐

  1. 索引支援:樞紐欄位 (`Book`)、分組欄位 (`SalesPerson`)、聚合欄位 (`Qty`) 建立適當索引 (如 `IX_Sales_Person_Book (SalesPerson, Book) INCLUDE (Qty)`)。
  2. 資料縮減:動態 SQL 前先篩選必要資料 (如日期範圍),減少掃描量。
  3. 緩存計畫:動態 SQL 文本每次不同會產生新計畫。若列值集合固定 (如年度月份),考慮參數化或固定列表。
  4. NULL 處理:`ISNULL`/`COALESCE` 將 NULL 轉 0,避免前端報表顯示異常。
  5. 安全性:嚴禁直接拼接使用者輸入,務必用 `QUOTENAME`、`sp_executesql` 參數化。

結語

靜態 `PIVOT` 適合固定報表;動態 SQL + `QUOTENAME` + `FOR XML PATH`/`STRING_AGG` (2017+) 是實現「任意列樞紐」的標準模式;**條件聚合** 則是效能與彈性的最佳平衡點。根據報表變動頻率、資料量、維護成本三角權衡選擇方案。

copyright © 萬盛學電腦網 all rights reserved