question

MohamedFarook-5287 avatar image
0 Votes"
MohamedFarook-5287 asked Viorel-1 edited

Multiple row Json value to Row

HI,
create table #temp (ID int,NameList nvarchar(max),Sort int)

insert into #temp (ID,NameList,Sort) VALUES (1001,'[{"User":"AAA"},{"User":"BBB"},{"User":"CCC"},{"User":"DDD"}]',1)
insert into #temp (ID,NameList,Sort) VALUES (1002,'[{"User":"EEE"}]',2)
insert into #temp (ID,NameList,Sort) VALUES (1002,'[{"User":"FFF"},{"User":"GGG"}]',3)

--Select

drop table #temp

i want result like below screen shot.

117368-image.jpg


sql-server-general
image.jpg (114.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

Try this:

 select ID, json_value([value], '$.User') as NameList, Sort
 from #temp
 cross apply openjson(NameList)
 order by ID, [key]


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.