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 

Answer 2

1. Create the UDF:

CREATE FUNCTION CombineValues (     @FK_ID INT -- The foreign key from TableA which is used                 -- to fetch corresponding records ) RETURNS VARCHAR(8000) AS BEGIN DECLARE @SomeColumnList VARCHAR(8000);  SELECT @SomeColumnList =     COALESCE(@SomeColumnList + ', ', '') + CAST(SomeColumn AS varchar(20))  FROM TableB C WHERE C.FK_ID = @FK_ID;  RETURN  (     SELECT @SomeColumnList ) END 

2. Use in subquery:

SELECT ID, Name, dbo.CombineValues(FK_ID) FROM TableA 

3. If you are using stored procedure you can do like this:

CREATE PROCEDURE GetCombinedValues  @FK_ID int As BEGIN DECLARE @SomeColumnList VARCHAR(800) SELECT @SomeColumnList =     COALESCE(@SomeColumnList + ', ', '') + CAST(SomeColumn AS varchar(20))  FROM TableB WHERE FK_ID = @FK_ID   Select *, @SomeColumnList as SelectedIds     FROM          TableA     WHERE          FK_ID = @FK_ID  END