SQL Server中创建带多聚合函数的动态PIVOT表求助

我明白你现在的困境：静态透视没法处理120天的动态列，而且需要把Param1、Param2按日期转成横向列。下面我给你一个完整的动态SQL解决方案，完美匹配你的需求：

核心思路 #

要实现动态透视，我们需要：

• 动态生成所有目标日期对应的Param1:YYYY-MM-DD和Param2:YYYY-MM-DD列
• 用CASE语句配合聚合函数（这里用MAX，因为你提到每个参数有预定义值，且每个用户每天应该只有一条记录）来提取对应日期的参数值
• 通过动态SQL拼接并执行完整查询

完整动态SQL代码 #

DECLARE @DynamicColumns NVARCHAR(MAX)
DECLARE @StartDate DATE = '2019-01-01' -- 你的起始日期
DECLARE @EndDate DATE = DATEADD(DAY, 119, @StartDate) -- 120天后的结束日期

-- 第一步：动态生成所有日期对应的Param1和Param2列的CASE语句
SELECT @DynamicColumns = STRING_AGG(
    CONCAT(
        'MAX(CASE WHEN [Date] = ''', CONVERT(NVARCHAR(10), DateVal, 23), ''' THEN [Param1] ELSE NULL END) AS [Param1:', CONVERT(NVARCHAR(10), DateVal, 23), '],',
        'MAX(CASE WHEN [Date] = ''', CONVERT(NVARCHAR(10), DateVal, 23), ''' THEN [Param2] ELSE NULL END) AS [Param2:', CONVERT(NVARCHAR(10), DateVal, 23), ']'
    ),
    ','
)
FROM (
    -- 生成120天的日期序列
    SELECT DATEADD(DAY, number, @StartDate) AS DateVal
    FROM master.dbo.spt_values
    WHERE type = 'P' AND number BETWEEN 0 AND 119
) AS DateList

-- 第二步：拼接完整的动态SQL语句
DECLARE @DynamicSQL NVARCHAR(MAX) = CONCAT(
    'SELECT [UserId], [LastName], [FirstName], ', @DynamicColumns, '
     FROM [dbo].[MyTable]
     GROUP BY [UserId], [LastName], [FirstName]
     ORDER BY [UserId]'
)

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

代码解释 #

1. 日期序列生成：用master.dbo.spt_values系统表生成120天的日期（从@StartDate开始），如果你用的是SQL Server 2022+，也可以用GENERATE_SERIES更简洁。
2. 动态列拼接：通过STRING_AGG把每个日期对应的两个CASE语句拼接成列列表，每个日期生成Param1:YYYY-MM-DD和Param2:YYYY-MM-DD两个列。
3. 聚合函数选择：用MAX是因为每个(UserId, Date)组合应该只有一条记录，MAX能准确提取对应参数值；如果存在多条记录，你可以根据需求换成MIN、COUNT或其他聚合函数（这就是你提到的3种聚合函数的应用场景）。
4. 执行动态SQL：用sp_executesql执行拼接好的SQL语句，避免SQL注入风险。

兼容旧版本SQL Server的调整 #

如果你的SQL Server版本低于2017，STRING_AGG不支持，需要换成FOR XML PATH的方式拼接字符串，替换SELECT @DynamicColumns = STRING_AGG(...)为：

SELECT @DynamicColumns = STUFF((
    SELECT ',' + CONCAT(
        'MAX(CASE WHEN [Date] = ''', CONVERT(NVARCHAR(10), DateVal, 23), ''' THEN [Param1] ELSE NULL END) AS [Param1:', CONVERT(NVARCHAR(10), DateVal, 23), '],',
        'MAX(CASE WHEN [Date] = ''', CONVERT(NVARCHAR(10), DateVal, 23), ''' THEN [Param2] ELSE NULL END) AS [Param2:', CONVERT(NVARCHAR(10), DateVal, 23), ']'
    )
    FROM (
        SELECT DATEADD(DAY, number, @StartDate) AS DateVal
        FROM master.dbo.spt_values
        WHERE type = 'P' AND number BETWEEN 0 AND 119
    ) AS DateList
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

额外注意事项 #

• 确保[Date]列的格式是DATE类型，避免日期格式不匹配的问题。
• 如果需要扩展Param3等其他参数，只需在动态列拼接部分添加对应的CASE语句即可。

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