Multiple row Json value to Row

Question

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.

Answer 1

Try this:

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