sql server - Combine multiple results in a subquery into a single comma-separated value

Question

I've got two tables:

TableA
------
ID,
Name

TableB
------
ID,
SomeColumn,
TableA_ID (FK for TableA)

The relationship is one row of TableA - many of TableB.

Now, I want to see a result like this:

ID     Name      SomeColumn

1.     ABC       X, Y, Z (these are three different rows)
2.     MNO       R, S

This won't work (multiple results in a subquery):

SELECT ID,
       Name, 
       (SELECT SomeColumn FROM TableB WHERE F_ID=TableA.ID)
FROM TableA

This is a trivial problem if I do the processing on the client side. But this will mean I will have to run X queries on every page, where X is the number of results of TableA.

Note that I can't simply do a GROUP BY or something similar, as it will return multiple results for rows of TableA.

I'm not sure if a UDF, utilizing COALESCE or something similar might work?

Answer 1

Even this will serve the purpose

Sample data

declare @t table(id int, name varchar(20),somecolumn varchar(MAX))
insert into @t
    select 1,'ABC','X' union all
    select 1,'ABC','Y' union all
    select 1,'ABC','Z' union all
    select 2,'MNO','R' union all
    select 2,'MNO','S'

Query:

SELECT ID,Name,
    STUFF((SELECT ',' + CAST(T2.SomeColumn AS VARCHAR(MAX))
     FROM @T T2 WHERE T1.id = T2.id AND T1.name = T2.name
     FOR XML PATH('')),1,1,'') SOMECOLUMN
FROM @T T1
GROUP BY id,Name

Output:

ID  Name    SomeColumn
1   ABC     X,Y,Z
2   MNO     R,S