sql

SQL - join by splitting the ID value

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
Solution

  • For SQL-Server 2017+ one solution would use CHARINDEX and String_AGG

    fiddle

    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