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

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,'') SOMECOLUMNFROM @T T1GROUP BY id,Name

Output:

ID  Name    SomeColumn1   ABC     X,Y,Z2   MNO     R,S

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)ASBEGINDECLARE @SomeColumnList VARCHAR(8000);SELECT @SomeColumnList =    COALESCE(@SomeColumnList + ', ', '') + CAST(SomeColumn AS varchar(20)) FROM TableB CWHERE 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 intAsBEGINDECLARE @SomeColumnList VARCHAR(800)SELECT @SomeColumnList =    COALESCE(@SomeColumnList + ', ', '') + CAST(SomeColumn AS varchar(20)) FROM TableBWHERE FK_ID = @FK_ID Select *, @SomeColumnList as SelectedIds    FROM         TableA    WHERE         FK_ID = @FK_ID END

In MySQL there is a group_concat function that will return what you're asking for.

SELECT TableA.ID, TableA.Name, group_concat(TableB.SomeColumn) as SomColumnGroup FROM TableA LEFT JOIN TableB ON TableB.TableA_ID = TableA.ID