Create MULTIPLE ROWS TO COMMA Separator

Question

Sample data:

Table A:

  BillID    ID_B
  --------------
  122        1
  122        2

Table B:

  ID_B     Name         
  ------------------
    1      Jakarta              
    2      Malang
    3      Wonosobo

Expected result:

 BillID     Name
 ------           -----------------
  122         Jakarta, Malang

TableA.ID_B = TableB.ID_B

E10 · Answer 1 · 2017-09-28T05:27:50.540

1

SELECT STUFF((
    SELECT
        ',' + [Name]
    FROM
        B AS B
    WHERE
        B.ID_B IN (SELECT ID_B FROM A)
    FOR XML PATH('')), 1, 1, '') AS [Name]

edited Sep 28 '17 at 05:27

answered Sep 28 '17 at 05:18

E10

874
1
7
8

score 1 · Accepted Answer · edited Sep 28 '17 at 11:17

1

SELECT BillID,STUFF((SELECT ',' + Name
FROM TABLEB AS T2
WHERE T2.ID_B = T1.ID_B
FOR XML PATH('')),1,1) AS Name FROM TABLEA AS T1

Hope this helps!

edited Sep 28 '17 at 11:17

this.hart

218
2
9

answered Sep 28 '17 at 09:18

Srinu Devireddy

36
4

Create MULTIPLE ROWS TO COMMA Separator

2 Answers2