T-SQL按userId和date透视数据时如何将其余字段聚合为JSON数组

你所述的需求不需要使用传统PIVOT语法，直接借助SQL Server 2016及以上版本原生支持的FOR JSON子句，配合分组关联查询即可实现，具体实现逻辑如下：

假设你的源表名为user_sessions，查询语句如下：

SELECT 
    userId,
    [date],
    (
        SELECT 
            sessionStartTime, 
            SessionEndTime, 
            nClicks
        FROM user_sessions t2
        WHERE 
            t2.userId = t1.userId 
            AND t2.[date] = t1.[date]
        -- 可选：按会话开始时间排序数组内的会话顺序
        ORDER BY sessionStartTime
        FOR JSON PATH
    ) AS data
FROM user_sessions t1
GROUP BY userId, [date]

语法说明 #

• FOR JSON PATH：会自动将子查询的多行结果，转换为[{"字段1":"值1","字段2":"值2"},...]格式的JSON数组，无需额外拼接
• 子查询的WHERE条件用于限定仅匹配当前分组下的userId和date对应的会话记录
• 如果需要自定义JSON的键名，可以在子查询中给列设置别名，例如将sessionStartTime写为sessionStartTime AS startTime，生成的JSON就会使用startTime作为键名

输出示例 #

如果某用户2023-10-01存在2条会话记录，最终返回的data列值如下：

[{"sessionStartTime":"2023-10-01 08:12:00","SessionEndTime":"2023-10-01 08:25:00","nClicks":12},{"sessionStartTime":"2023-10-01 14:03:00","SessionEndTime":"2023-10-01 14:17:00","nClicks":8}]

注意：如果使用的是SQL Server 2016之前的版本，没有原生JSON支持，需要手动拼接字符串构造数组，兼容性和稳定性较差，建议升级到支持原生JSON的版本使用上述方案。

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