原始交易明細表 `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
-- 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() 可重用計畫。 |
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 樞紐分析服務 更靈活。