主页 / dba / 问题 / 317376
Accepted
Siva Dasari
Asked: 2022-09-26 11:30:35 +0800 CST

在 TSQL 中将行转换为所需的 JSON 格式

  • 772

尝试将数据转换为所需的 JSON 但未成功，我们将不胜感激任何帮助或建议。

SQL Server 版本：2019

测试脚本：

drop table if exists t1
go
create table t1(id int, key int, value varchar(10))
go
insert into t1 values (100, 1, 'value11')
insert into t1 values (100, 2, 'value12')
insert into t1 values (100, 3, 'value13')
insert into t1 values (100, 4, 'value14')
go
insert into t1 values (200, 1, 'value21')
insert into t1 values (200, 2, 'value22')
insert into t1 values (200, 3, 'value23')
insert into t1 values (200, 4, 'value24')
go
select  
    t.id, t.key, t.value
from t1 as t
for json path,  WITHOUT_ARRAY_WRAPPER

输出：

{"id":100,"key":1,"value":"value11"},
{"id":100,"key":2,"value":"value12"},
{"id":100,"key":3,"value":"value13"},
{"id":100,"key":4,"value":"value14"},
{"id":200,"key":1,"value":"value21"},
{"id":200,"key":2,"value":"value22"},
{"id":200,"key":3,"value":"value23"},
{"id":200,"key":4,"value":"value24"}

期望的输出：

100, {"1":"value11","2":"value12","3":"value13","4":"value14"}

200, {"1":"value21","2":"value22","3":"value23","4":"value24"}
sql-server t-sql

1 个回答

  1. Best Answer

    FOR JSON 将数据（列值）转换为元数据（json 键）并不容易。但是使用 string_agg 很容易，例如：

    select t.id, 
    '{' + STRING_AGG(concat(quotename([key],'"'),':',quotename(value,'"')),',') within group( order by value ) + '}' value
from t1 as t
group by id 
order by id
    • 3

相关问题