SQL Server 動態樞紐分析：透過動態 SQL 實現資料透視表自動產生

樞紐分析 是將長表格轉為寬表格、依維度聚合指標的經典需求。SQL Server 原生 PIVOT 語法需靜態列出所有欄位值，無法應對「產品類別不固定」、「月份動態增減」等場景。本文示範如何結合動態 SQL、QUOTENAME、FOR XML PATH 自動產生 PIVOT 陳述式，實現真正的動態樞紐。

場景與基礎資料

CREATE TABLE SalesData (
    SalesPerson NVARCHAR(50),
    Product     NVARCHAR(50),
    Qty         INT
);

INSERT INTO SalesData VALUES
('小王','Excel教材',10), ('小李','Excel教材',15),
('小王','Word教材',8),  ('小李','Excel教材',7),
('小王','Excel教材',9), ('小李','Excel教材',2),
('小王','Word教材',3), ('小李','Excel教材',5);

目標：各業務員 × 產品銷量矩陣，產品欄位動態產生。

動態 PIVOT 核心腳本

DECLARE @Cols  NVARCHAR(MAX) = '',
        @Sql   NVARCHAR(MAX) = '';

-- 1. 收集所有不重複的樞紐欄位值，並安全轉義
SELECT @Cols += QUOTENAME(Product) + ','
FROM (SELECT DISTINCT Product FROM SalesData) d;

-- 移除尾隨逗號
SET @Cols = LEFT(@Cols, LEN(@Cols) - 1);

-- 2. 組裝動態 PIVOT 查詢
SET @Sql = N'
SELECT SalesPerson, ' + @Cols + N'
FROM (
    SELECT SalesPerson, Product, Qty
    FROM SalesData
) src
PIVOT (
    SUM(Qty) FOR Product IN (' + @Cols + N')
) pvt
ORDER BY SalesPerson;';

-- 3. 執行
EXEC sp_executesql @Sql;

執行結果

注意： 小李無 Word 教材銷售，PIVOT 預設回傳 NULL，可用 ISNULL(欄位,0) 包裝外層 SELECT。

進階：多彙總指標、多維度樞紐

-- 同時顯示 銷量SUM、筆數COUNT、平均AVG
DECLARE @AggCols NVARCHAR(MAX) = '';
SELECT @AggCols += 
    'ISNULL(' + QUOTENAME(Product + '_Sum')   + ',0) AS ' + QUOTENAME(Product + '_銷量') + ','
  + 'ISNULL(' + QUOTENAME(Product + '_Cnt')   + ',0) AS ' + QUOTENAME(Product + '_筆數') + ','
  + 'ISNULL(' + QUOTENAME(Product + '_Avg')   + ',0) AS ' + QUOTENAME(Product + '_均量') + ','
FROM (SELECT DISTINCT Product FROM SalesData) d;
SET @AggCols = LEFT(@AggCols, LEN(@AggCols) - 1);

SET @Sql = N'
SELECT SalesPerson, ' + @AggCols + N'
FROM (
    SELECT SalesPerson, Product, Qty
    FROM SalesData
) src
PIVOT (SUM(Qty) FOR Product IN (' + @Cols + N')) pvt_sum
PIVOT (COUNT(Qty) FOR Product IN (' + @Cols + N')) pvt_cnt
PIVOT (AVG(Qty*1.0) FOR Product IN (' + @Cols + N')) pvt_avg
ORDER BY SalesPerson;';
-- 注意：多重 PIVOT 需鏈接，實務上建議用條件聚合 (CASE WHEN) 替代，效能更穩定

條件聚合替代方案 (效能更優、語法更彈性)

DECLARE @CaseExpr NVARCHAR(MAX) = '';
SELECT @CaseExpr += 
    'SUM(CASE WHEN Product = ' + QUOTENAME(Product, '''') + ' THEN Qty END) AS ' + QUOTENAME(Product + '_銷量') + ','
  + 'COUNT(CASE WHEN Product = ' + QUOTENAME(Product, '''') + ' THEN 1 END) AS ' + QUOTENAME(Product + '_筆數') + ','
FROM (SELECT DISTINCT Product FROM SalesData) d;
SET @CaseExpr = LEFT(@CaseExpr, LEN(@CaseExpr) - 1);

SET @Sql = N'SELECT SalesPerson, ' + @CaseExpr + N' FROM SalesData GROUP BY SalesPerson ORDER BY SalesPerson;';
EXEC sp_executesql @Sql;

優勢： 單次掃描、無 PIVOT 運算子開銷、易擴充多指標、支援複雜條件 (如同時按區域、年份雙維度樞紐)。

實務防護：防止 SQL 注入與識別碼過長

• 欄位值經 QUOTENAME 轉義，防止惡意輸入如 Product'; DROP TABLE SalesData--
• 動態 SQL 總長度勿超過 8000 (NVARCHAR(MAX) 理論 2GB，但編譯記憶體有限)
• 樞紐欄位過多 (超過 100) 時，考慮改為應用層轉置或報表工具處理

擴充：月份動態樞紐 (年度跨度不固定)

DECLARE @MonthCols NVARCHAR(MAX) = '';
SELECT @MonthCols += QUOTENAME(FORMAT(SaleDate, 'yyyy-MM')) + ','
FROM (SELECT DISTINCT FORMAT(SaleDate, 'yyyy-MM') FROM SalesData) d
ORDER BY MIN(SaleDate); -- 確保時間順序
SET @MonthCols = LEFT(@MonthCols, LEN(@MonthCols) - 1);

SET @Sql = N'SELECT SalesPerson, ' + @MonthCols + N'
FROM (SELECT SalesPerson, FORMAT(SaleDate,''yyyy-MM'') AS YM, Qty FROM SalesData) src
PIVOT (SUM(Qty) FOR YM IN (' + @MonthCols + N')) pvt ORDER BY SalesPerson;';
EXEC sp_executesql @Sql;

動態樞紐技術，讓資料庫直接產出「報表就緒」的寬表格式，大幅降低應用層/報表工具的轉置負擔，是 BI 自助分析的關鍵推手。