Comma separate value matched on master table

Question

HI,

I need id matched name

create table #master (ID varchar(100),_Name nvarchar(255))
insert into #master (ID,_Name) values ('1001','AAA'),('1002','BBB'),('1003','CCC'),('1004','DDD'),('1005','EEE')

create table #temp1 (ID varchar(100),_Name nvarchar(255))
insert into #temp1 (ID,_Name) values ('1001,1002,1003,1005',''),('1001,1002','')

select * from #temp1

drop table #temp1,#master

Exact output like this

ID _Name
1001,1002,1003,1005 AAA,BBB,CCC,EEE
1001,1002 AAA,BBB

Answer 1

Try:

;WITH cte AS (SELECT ROW_NUMBER() OVER (ORDER BY _Name) AS RowId, ID, _Name FROM #temp1)  
,cte1 AS (SELECT cte.RowID, cte.ID, m._Name, s.value FROM cte CROSS APPLY  STRING_SPLIT(cte.ID, ',') s   
INNER JOIN #master m ON s.value = m.ID)  
  
SELECT ID, STRING_AGG(_Name, ',') WITHIN GROUP (ORDER BY _NAME) AS _Name  
FROM cte1 GROUP BY ID   

Answer 2

create table #master (ID varchar(100),_Name nvarchar(255))  
insert into #master (ID,_Name) values ('1001','AAA'),('1002','BBB'),('1003','CCC'),('1004','DDD'),('1005','EEE')  
  
create table #temp1 (ID varchar(100),_Name nvarchar(255))  
insert into #temp1 (ID,_Name) values ('1001,1002,1003,1005',''),('1001,1002','')  
;with split as  
(  
select * from #temp1  
cross apply string_split(ID,',')  
)  
,mycte as (  
Select s.ID grp, m.ID,m._Name from split s   
join #master m on s.value=m.ID  
)  
  
select string_agg(ID,',') ID  
,string_agg(_Name,',') _Name  
from mycte  
   
Group by grp  
  
drop table #temp1,#master