The table structure is as below
TABLE A
ID | Code |
---|---|
1 | ABC |
2 | DEF |
3 | GHI |
4 | JKL |
TABLE B
ID | A_ID |
---|---|
10 | 1,2 |
11 | 3,4 |
How to join the 2 tables to return the final result as, should the A_ID in Table B be split and then joined
ID | Code |
---|---|
10 | ABC, DEF |
11 | GHI, JKL |
For SQL-Server 2017+ one solution would use CHARINDEX and String_AGG
SELECT B.ID, STRING_AGG(A.Code,',') as CODE
FROM TABLEA A
INNER JOIN TABLEB B On CHARINDEX(',' + A.ID + ',', ',' + B.A_ID +',') > 0
GROUP BY B.ID
Before 2017 you could use STUFF and FOR XML PATH:
SELECT B.ID, STUFF((SELECT ', ' + Code FROM TABLEA
WHERE CHARINDEX(',' + TABLEA.ID + ',', ',' + B.A_ID +',') > 0 ORDER BY Code FOR XML PATH(''),TYPE).value('.','varchar(MAX)'),1,2,'')
FROM TABLEB B