请问sql for json path如何控制查询结果输出为单个列名形式

使用以下命令建立一个测试表:
create table #temp1 (id int,vars char(1))
insert into #temp1 (id,vars) values (1,'a')
insert into #temp1 (id,vars) values (2,'b')
select id,vars from #temp1 for json path
输出结果为:[{"id":1,"vars":"a"},{"id":2,"vars":"b"}]
这不是我想要的,因为列名会重复出现而增加json文本长度,在多行数据时很明显.
我想要这样的输出:[{"id": [1, 2]},{"name": ["a", "b"]}],列名做为键名只出现一次,字段值做为数组输出

请问如何实现?

select '[{"id":['+stuff((select ','+cast(id as varchar)  from temp1 
for xml path('')),1,1,'') +'],"vars":['+stuff((select ',"'+(vars)+'"' from temp1 
for xml path('')),1,1,'') +']}]'

实测截图,和你要的结果一模一样

img

测试地址

list其实是无序的,你分开两个数组,很有可能会导致数据无法对应。json和xml一样,一直是有大量冗余的字符,但这就是保证兼容性的标准。自己定解析规则也不是不行,但这样就无法兼容其他外部接口了,只能你自己内部使用

--带子集

SELECT '张学友' name, 60 age,'你好毒' AS [专辑.流行],'如果这都不算爱' AS [专辑.经典] FOR JSON PATH


--返回值

[{"name":"张学友","age":60,"专辑":{"流行":"你好毒","经典":"如果这都不算爱"}}]

能否对数据进行适当的调整
就是把你的temp1表进行整理后，然后在输出。
create table #temp1 (id varchar(100),vars char(100))
insert into #temp1 (id,vars) values ('1,2','a,b')

这个SQL实现不了，只能用代码逻辑处理。