Hi kaushal,
Refer the below query.
DECLARE @table1 AS TABLE(ID INT, NAME VARCHAR(50))
INSERT @table1(Id, NAME) VALUES (1,'abc')
INSERT @table1(Id, NAME) VALUES (2,'pqr')
INSERT @table1(Id, NAME) VALUES (3,'lmn')
INSERT @table1(Id, NAME) VALUES (4,'xyz')
INSERT @table1(Id, NAME) VALUES (5,'lll')
INSERT @table1(Id, NAME) VALUES (6,'yyy')
INSERT @table1(Id, NAME) VALUES (7,'ttt')
INSERT @table1(Id, NAME) VALUES (8,'demo')
DECLARE @table2 AS TABLE(ID INT, ITEMCODE VARCHAR(50))
INSERT @table2(Id, itemcode) VALUES (1,'1,2,3,8')
INSERT @table2(Id, itemcode) VALUES(2,'1,2')
INSERT @table2(Id, itemcode) VALUES(3,'2,3')
INSERT @table2(Id, itemcode) VALUES(4,'2,3,1,4,5')
INSERT @table2(Id, itemcode) VALUES(5,'2,3,5,4')
INSERT @table2(Id, itemcode) VALUES(6,'2,3,6,3')
INSERT @table2(Id, itemcode) VALUES(7,'2,3,7,4')
SELECT ID, ItemCode = STUFF((SELECT ',' + t1.NAME
FROM @table1 AS t1
INNER JOIN @table2 AS t2
ON ',' + t2.itemcode + ',' LIKE '%,' + CONVERT(VARCHAR(250),t1.id) + ',%'
WHERE t2.ID = t21.ID
FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
FROM @table2 AS t21
Output
ID |
ItemCode |
1 |
abc,pqr,lmn,demo |
2 |
abc,pqr |
3 |
pqr,lmn |
4 |
abc,pqr,lmn,xyz,lll |
5 |
pqr,lmn,xyz,lll |
6 |
pqr,lmn,yyy |
7 |
pqr,lmn,xyz,ttt |