SQL Server 2005 PIVOT 動態行轉列完整實作：從靜態語法到動態 SQL 自動化產生欄位

SQL Server 2005 引入 PIVOT 運算子，支援行轉列。本文透過銷售資料範例，示範靜態 PIVOT 語法、動態 SQL 自動產生欄位清單、儲存程序封裝，解決「欄位不固定、需依資料動態決定」的實務難題。

範例資料

CREATE TABLE tb (
  ID VARCHAR(50), CName VARCHAR(50), Amount MONEY, ActionDate DATETIME
);
INSERT INTO tb VALUES
('001','a',2000.00,'2008-02-28'), ('001','a',1560.00,'2008-03-28'),
('001','a',2040.00,'2008-01-28'), ('001','a',1003.00,'2008-04-28'),
('101','b',3100.00,'2008-03-28'), ('101','b',4040.00,'2008-01-28'),
('101','b',5003.00,'2008-04-28'), ('101','b',2400.00,'2008-02-28'),
('001','a',15000.00,'2007-12-21'), ('101','b',22400.00,'2007-12-21');

目標輸出

ID  CName  2007-12   2008-01   2008-02   2008-03   2008-04
001 a      15000.00  2040.00   2000.00   1560.00   1003.00
101 b      22400.00  4040.00   2400.00   3100.00   5003.00

實作步驟

步驟 1：彙總並格式化日期 (建立暫存表 #tmp)

SELECT ID, CName, SUM(Amount) AS Amount,
       LEFT(CONVERT(VARCHAR(20), ActionDate, 120), 7) AS ActionDate  -- YYYY-MM
INTO #tmp
FROM tb
GROUP BY ID, CName, LEFT(CONVERT(VARCHAR(20), ActionDate, 120), 7);

步驟 2：動態產生 PIVOT IN 清單

DECLARE @sql VARCHAR(MAX) = '';
SELECT @sql = @sql + '[' + ActionDate + '],'
FROM #tmp
GROUP BY ActionDate;
SET @sql = LEFT(@sql, LEN(@sql) - 1);  -- 移除尾隨逗號
-- 結果範例：[2007-12],[2008-01],[2008-02],[2008-03],[2008-04]

步驟 3：建構完整動態 PIVOT 查詢

DECLARE @sqlAll NVARCHAR(MAX) = '
SELECT * FROM #tmp s
PIVOT (SUM(Amount) FOR ActionDate IN (' + @sql + ')) AS P
ORDER BY ID, CName;';

PRINT @sqlAll;  -- 除錯用
EXEC sp_executesql @sqlAll;

步驟 4：清理

DROP TABLE #tmp;

進階封裝：通用動態 PIVOT 儲存程序

CREATE PROC sp_DynamicPivot
  @SourceTable NVARCHAR(128),      -- 來源表/暫存表
  @RowFields NVARCHAR(MAX),        -- 列欄位 (如 'ID, CName')
  @PivotField NVARCHAR(128),       -- 轉欄位 (如 'ActionDate')
  @ValueField NVARCHAR(128),       -- 值欄位 (如 'Amount')
  @AggFunc NVARCHAR(20) = 'SUM'    -- 聚合函數
AS
BEGIN
  DECLARE @cols NVARCHAR(MAX) = '';
  DECLARE @sql NVARCHAR(MAX);

  -- 動態取得欄位值
  SET @sql = 'SELECT @cols = COALESCE(@cols + ''],['', '''') + QUOTENAME(' + @PivotField + ') 
              FROM ' + @SourceTable + ' GROUP BY ' + @PivotField;
  EXEC sp_executesql @sql, N'@cols NVARCHAR(MAX) OUTPUT', @cols OUTPUT;

  IF @cols = '' RETURN;  -- 無資料

  SET @sql = 'SELECT ' + @RowFields + ', ' + @cols + '
              FROM ' + @SourceTable + '
              PIVOT (' + @AggFunc + '(' + @ValueField + ') FOR ' + @PivotField + ' IN (' + @cols + ')) AS P
              ORDER BY ' + @RowFields;

  EXEC sp_executesql @sql;
END;

呼叫範例

-- 先建立彙總暫存表
SELECT ID, CName, SUM(Amount) AS Amount,
       LEFT(CONVERT(VARCHAR(20), ActionDate, 120), 7) AS ActionDate
INTO #pivot_src FROM tb GROUP BY ID, CName, LEFT(CONVERT(VARCHAR(20), ActionDate, 120), 7);

EXEC sp_DynamicPivot '#pivot_src', 'ID, CName', 'ActionDate', 'Amount', 'SUM';
DROP TABLE #pivot_src;

注意事項

• PIVOT 需配合聚合函數，重複鍵值會自動彙總。
• 動態 SQL 需防注入：欄位名用 QUOTENAME，值參數化。
• 欄位值過長或含特殊字元可能超過識別碼長度限制 (128 字元)，需截斷或雜湊處理。
• 大量欄位 (超過 4096) 會觸發「查詢處理器資源不足」，建議前端分頁或分組顯示。

掌握動態 PIVOT 技巧，能將固定報表邏輯轉化為通用元件，大幅減少手寫轉換 SQL 的維護成本。