SQL Server：按分组将查询列转换为JSON对象

看了你的测试数据和需求，我整理了几种不同结构的JSON输出方案，你可以根据实际需要选择：

1. 按ID聚合，嵌套ValueV对应的KEYS列表 #

这个方案会把同一个ID下的数据按ValueV分组，生成层级清晰的嵌套JSON：

SELECT 
    ID,
    (
        SELECT 
            ValueV,
            STRING_AGG(KEYS, ', ') AS KeysList
        FROM #Test t2
        WHERE t2.ID = t1.ID
        GROUP BY ValueV
        FOR JSON PATH
    ) AS ValueGroups
FROM #Test t1
GROUP BY ID
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

2. 每个(ValueV, ID)组作为独立JSON对象 #

如果需要更扁平的结构，每个ValueV+ID组合对应一个JSON对象：

SELECT 
    ID,
    ValueV,
    STRING_AGG(KEYS, ', ') AS KeysList
FROM #Test
GROUP BY ID, ValueV
FOR JSON PATH;

3. 将KEYS转为JSON数组而非字符串 #

要是希望KeysList是标准的JSON数组（而不是逗号分隔的字符串），可以用子查询生成数组：

SELECT 
    ID,
    (
        SELECT 
            ValueV,
            (SELECT KEYS FROM #Test t3 WHERE t3.ID = t2.ID AND t3.ValueV = t2.ValueV FOR JSON PATH) AS KeysArray
        FROM #Test t2
        WHERE t2.ID = t1.ID
        GROUP BY ValueV
        FOR JSON PATH
    ) AS ValueGroups
FROM #Test t1
GROUP BY ID
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

补充说明 #

• STRING_AGG是SQL Server 2017及以上版本支持的聚合函数，用来合并字符串；如果你的版本低于2017，可以用STUFF + FOR XML PATH的写法替代：

  -- 替代STRING_AGG的写法
  STUFF((SELECT ', ' + KEYS FROM #Test t2 WHERE t2.ID = t1.ID AND t2.ValueV = t1.ValueV FOR XML PATH('')), 1, 2, '') AS KeysList
  

• FOR JSON PATH是SQL Server自带的JSON生成工具，WITHOUT_ARRAY_WRAPPER参数会移除最外层的数组括号，让输出更符合单个对象或对象集合的格式。

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