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

1. Create the UDF:

CREATE FUNCTION CombineValues(
@F_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 CWHERE C.FK_ID = @FK_ID;
RETURN (
SELECT @SomeColumnList)
END

2. Use in subquery:

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


ref :http://stackoverflow.com/questions/111341/combine-multiple-results-in-a-subquery-into-a-single-comma-separated-value