萬盛學電腦網

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

SQL Server 2005 PIVOT 動態行轉欄完整範例:銷售月報表產生

需求場景

原始交易明細表 `tb`:

ID    CName   Amount    ActionDate
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

動態 PIVOT 實作腳本 (適用月份不固定)

-- 1. 彙總基礎資料至暫存表 (按 年-月 分組)
SELECT 
    ID, 
    CName, 
    SUM(Amount) AS Amount,
    LEFT(CONVERT(VARCHAR(20), ActionDate, 120), 7) AS YearMonth -- 'YYYY-MM'
INTO #tmp
FROM tb
GROUP BY ID, CName, LEFT(CONVERT(VARCHAR(20), ActionDate, 120), 7);

-- 2. 動態產生欄位清單 [2007-12],[2008-01],...
DECLARE @Columns NVARCHAR(MAX) = N'';
SELECT @Columns += QUOTENAME(YearMonth) + N','
FROM #tmp
GROUP BY YearMonth
ORDER BY YearMonth; -- 確保欄位順序

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

-- 3. 組裝動態 PIVOT SQL
DECLARE @Sql NVARCHAR(MAX) = N'
SELECT * FROM #tmp src
PIVOT (
    SUM(Amount) FOR YearMonth IN (' + @Columns + N')
) AS pvt
ORDER BY ID, CName;';

-- 4. 執行
EXEC sp_executesql @Sql;

-- 5. 清理
DROP TABLE #tmp;

關鍵語法解析

子句說明
PIVOT (聚合函數 FOR 欄位 IN (欄位清單))將行資料旋轉為欄位,聚合函數通常為 SUM/COUNT/AVG。
QUOTENAME()自動加上 `[]` 括號,防止欄位名含特殊字元或保留字報錯。
sp_executesql執行動態 SQL,支援參數化,優於 EXEC() 可重用計畫。

靜態 PIVOT 寫法 (欄位固定已知)

SELECT ID, CName, 
    [2007-12], [2008-01], [2008-02], [2008-03], [2008-04]
FROM (
    SELECT ID, CName, Amount, LEFT(CONVERT(VARCHAR(7), ActionDate, 120), 7) AS YearMonth
    FROM tb
) AS src
PIVOT (SUM(Amount) FOR YearMonth IN ([2007-12],[2008-01],[2008-02],[2008-03],[2008-04])) AS pvt
ORDER BY ID, CName;

進階應用:多指標交叉表

若需同時顯示 `銷售額` 與 `筆數`,可使用 `CROSS APPLY` 或 `UNION ALL` 將指標轉為行後再 PIVOT,或改用 SSRS/Excel 樞紐分析服務 更靈活。

效能提示

  • 來源表建立覆蓋索引:`CREATE INDEX IX_tb_Pivot ON tb (ID, CName, ActionDate) INCLUDE (Amount);`
  • 暫存表 `#tmp` 可加 `PRIMARY KEY (ID, CName, YearMonth)` 加速 PIVOT 讀取。
  • 動態 SQL 每次編譯計畫,高頻呼叫建議快取產生的 `@Columns` 或改用應用層組裝。
copyright © 萬盛學電腦網 all rights reserved