您现在的位置：程式碼之家 >> 編程語言 > >> 更多程式語言 >> 程式設計綜合問答

SQL Server 2005 行轉列實現：縱表轉橫表的 PIVOT 與動態 SQL 完整方案

編輯：程式設計綜合問答

業務場景

原始縱表：每行一條記錄，包含 id、星期、預算、實際。需轉為橫表：每行一個 id，列為各星期的預算/實際。

方案一：靜態 PIVOT (SQL Server 2005+)

SELECT 
  id,
  ISNULL([1], 0) AS 星期一預算, ISNULL([2], 0) AS 星期一實際,
  ISNULL([3], 0) AS 星期二預算, ISNULL([4], 0) AS 星期二實際,
  -- ... 依此類推至星期日
FROM (
  SELECT id, 
         CASE WHEN 項目 = '預算' THEN 星期 * 2 - 1 ELSE 星期 * 2 END AS col_idx,
         數值
  FROM 縱表
) src
PIVOT (
  SUM(數值) FOR col_idx IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14])
) pvt;

方案二：動態 SQL 生成列（列名不固定時）

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- 生成動態列清單
SELECT @cols = STUFF((
  SELECT DISTINCT ', ' + QUOTENAME(CAST(星期 AS VARCHAR) + '_' + 項目)
  FROM 縱表
  ORDER BY 1
  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

-- 構建完整查詢
SET @sql = N'
SELECT id, ' + @cols + N'
FROM (
  SELECT id, 
         CAST(星期 AS VARCHAR) + '_' + 項目 AS col_name,
         數值
  FROM 縱表
) src
PIVOT (
  SUM(數值) FOR col_name IN (' + @cols + N')
) pvt';

EXEC sp_executesql @sql;

方案三：條件聚合（通用、性能佳、無版本限制）

SELECT 
  id,
  SUM(CASE WHEN 星期 = 1 AND 項目 = '預算' THEN 數值 ELSE 0 END) AS 星期一預算,
  SUM(CASE WHEN 星期 = 1 AND 項目 = '實際' THEN 數值 ELSE 0 END) AS 星期一實際,
  SUM(CASE WHEN 星期 = 2 AND 項目 = '預算' THEN 數值 ELSE 0 END) AS 星期二預算,
  SUM(CASE WHEN 星期 = 2 AND 項目 = '實際' THEN 數值 ELSE 0 END) AS 星期二實際,
  -- ... 其餘星期
FROM 縱表
GROUP BY id;

性能與選型建議

方案	優點	缺點	推薦場景
靜態 PIVOT	語法標準、可讀性好	列固定、擴展需改代碼	報表列固定、如月度固定指標
動態 PIVOT	列自適應、無需改代碼	需動態 SQL、注入風險、難調試	用戶自定義報表列、元數據驅動
條件聚合	全數據庫通用、優化器友好、易維護	列固定時代碼較長	大多數業務場景首選