select Json related value and revert option

Question

I Hi,
I want one more changes
i want display Name related ID

--Query1--
create table #CIDValue (Slno int,CID varchar(100))
INSERT INTO #CIDValue (Slno,CID) VALUES (67,'AAA'),(68,'BBB'),(69,'CCC')

create table #temp (ID INT,Details nvarchar(200))
INSERT INTO #temp (ID,Details) VALUES (1001,'[{"CID":"CCC"}]'),
(1002,'[{"CID":"AAA"},{"CID":"BBB"},{"CID":"CCC"}]'),
(1003,'[{"CID":"AAA"},{"CID":"BBB"},{"CID":"CCC"}])')

select * from #temp
drop table #temp,#CIDValue

--I want result like below
--ID Details
--1001 69
--1002 67,68,69
--1003 67,68,69

--Query2--
create table #CIDValue1 (Slno int,CID varchar(100))
INSERT INTO #CIDValue1 (Slno,CID) VALUES (67,'AAA'),(68,'BBB'),(69,'CCC')
create table #temp1 (ID INT,Details nvarchar(200))
INSERT INTO #temp1 (ID,Details) VALUES (1001,'69'),
(1002,'67,68,69'),
(1003,'67,68,69')

select * from #temp1
drop table #temp1,#CIDValue1
--I want result like below
--ID Detail
--1001 [{"CID":"CCC"}]
--1002 [{"CID":"AAA"},{"CID":"BBB"},{"CID":"CCC"}]
--1003 [{"CID":"AAA"},{"CID":"BBB"},{"CID":"CCC"}])

pls help

Answer 1

Try these queries:

select ID, string_agg(c.Slno, ',') as Details
from #temp
cross apply openjson( Details, '$') with ( v varchar(max) '$.CID' )
inner join #CIDValue c on c.CID = v
group by ID

select ID, concat( '[', string_agg( j, ','), ']') as Details
from #temp1
cross apply string_split(Details, ',') s
inner join #CIDValue1 c on c.Slno = s.value
cross apply (values ((select c.CID as CID for json path, without_array_wrapper))) t(j)
group by ID

You can also discover that there are equivalent functions for STRING_AGG and STRING_SPLIT for old versions of SQL servers.