如何利用Dynamic Pivot将日期列转为列名实现动态透视？

刚好我之前处理过类似的场景，针对你这种未知日期列数量/值+数据源是子查询/CTEs的需求，给你一套可行的解决方案，下面以SQL Server为例（如果是MySQL、PostgreSQL等其他数据库，思路类似但语法有差异，可以告诉我调整）：

核心思路 #

1. 先从复杂数据源中提取所有唯一的日期，动态生成Pivot需要的列列表
2. 把复杂数据源（子查询/CTEs）封装到动态SQL内部，避免引用问题
3. 处理Pivot后出现的NULL值，转换为0以匹配你的目标表格式

完整代码示例 #

假设你的复杂数据源是一个CTE（你可以把下面的ComplexDataSource替换成你实际的子查询或CTEs逻辑）：

DECLARE @PivotColumns NVARCHAR(MAX), @Sql NVARCHAR(MAX);

-- 第一步：动态生成所有日期列（带引号，避免语法错误）
SELECT @PivotColumns = STRING_AGG(QUOTENAME([Date]), ', ')
FROM (SELECT DISTINCT [Date] FROM (
    -- 这里先放你的复杂数据源逻辑，比如子查询/CTEs
    SELECT App, [Date], [Count]
    FROM YourOriginalTable -- 替换成你的实际表或查询
) AS Temp) AS Dates;

-- 第二步：构建包含复杂数据源的动态SQL，同时处理NULL转0
SET @Sql = N'
WITH ComplexDataSource AS (
    -- 这里替换成你的实际子查询/CTEs逻辑
    SELECT App, [Date], [Count]
    FROM YourOriginalTable -- 替换成你的实际表或查询
)
SELECT 
    App,
    ' + STRING_AGG('ISNULL(' + QUOTENAME([Date]) + ', 0) AS ' + QUOTENAME([Date]), ', ') + '
FROM ComplexDataSource
PIVOT (
    SUM([Count]) -- 因为同一App+Date可能有多行，用SUM聚合，若确保唯一也可以用MAX
    FOR [Date] IN (' + @PivotColumns + ')
) AS PivotedResult
ORDER BY App;';

-- 第三步：执行动态SQL
EXEC sp_executesql @Sql;

关键细节说明 #

• 兼容旧版SQL Server：如果你的SQL Server版本低于2017（不支持STRING_AGG），可以用FOR XML PATH来拼接列：

  SELECT @PivotColumns = STUFF((
      SELECT ', ' + QUOTENAME([Date])
      FROM (SELECT DISTINCT [Date] FROM (
          -- 这里放你的复杂数据源逻辑
          SELECT [Date] FROM YourOriginalTable
      ) AS Temp) AS Dates
      ORDER BY [Date] -- 按日期排序，让表头更规范
      FOR XML PATH(''), TYPE
  ).value('.', 'NVARCHAR(MAX)'), 1, 2, '');
  

• 处理SQL注入：如果你的复杂数据源需要传入参数，不要直接拼接字符串，而是通过sp_executesql的参数传递功能，比如：

  EXEC sp_executesql @Sql, N'@Param1 INT', @Param1 = 123;
  

• 其他数据库适配：比如MySQL需要用GROUP_CONCAT拼接列，用PREPARE+EXECUTE执行动态SQL；PostgreSQL用string_agg拼接，用EXECUTE语句执行。

效果验证 #

执行后会生成你需要的目标表格式：App作为行，所有日期作为表头，对应的值为Count的聚合结果，空值自动转为0。

内容的提问来源于stack exchange，提问作者Will Stephenson