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