SQL Server：生成按分组键聚合且排除指定列的JSON结果

需求概述 #

需要生成满足以下要求的查询结果：

• 指定列GroupKey作为分组键单独展示，不包含在JSON内容里
• 其余所有列（如Data_A、Data_B等）按GroupKey分组后，整合成JSON格式数据
• 最终结果按GroupKey分组，每行对应一个分组键和对应的JSON内容

示例实现代码 #

固定列写法（SQL Server） #

如果明确知道需要包含的非分组列，可以直接子查询结合FOR JSON PATH实现：

SELECT
    GroupKey,
    (
        SELECT Data_A, Data_B, Data_C -- 列出所有需纳入JSON的列，排除GroupKey
        FROM T AS SubT
        WHERE SubT.GroupKey = T.GroupKey
        FOR JSON PATH
    ) AS JSON
FROM T
GROUP BY GroupKey

动态列写法（自动排除分组键） #

如果非分组列较多或不确定，可以用动态SQL自动获取并排除GroupKey：

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

-- 自动获取表T中除GroupKey外的所有列
SELECT @cols = STRING_AGG(QUOTENAME(COLUMN_NAME), ', ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'T' AND COLUMN_NAME != 'GroupKey'

-- 拼接并执行动态SQL
SET @sql = N'
SELECT
    GroupKey,
    (
        SELECT ' + @cols + '
        FROM T AS SubT
        WHERE SubT.GroupKey = T.GroupKey
        FOR JSON PATH
    ) AS JSON
FROM T
GROUP BY GroupKey'

EXEC sp_executesql @sql

预期结果 #

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