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

Niladri.Biswas
Posted by Niladri.Biswas under Sql Server category on | Points: 40 | Views : 1504
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



Solution
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'


Query:
select ID,Name,
stuff((select ',' + CAST(t2.SomeColumn as varchar(10))
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

Comments or Responses

Login to post response