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

Posted by Niladri.Biswas under Sql Server category on | Points: 40 | Views : 1393
I've got two tables:

ID, Name

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

Sample data
declare @t table(id int, name varchar(20),somecolumn varchar(10))
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'

select ID,Name,
stuff((select ',' + CAST(t2.SomeColumn as varchar(10))
from @t t2 where = and =
for xml path('')),1,1,'') SomeColumn
from @t t1
group by id,Name

Comments or Responses

Login to post response