[RESOLVED] Concatonating data in SQL server 2005

[wild_bill]

Given the following table, how could I create a query to return:

-------
|a|1,2|
-------

declare @table table(id char(1), sub_group int)
insert into @table (id, sub_group) values ('a',1)
insert into @table (id, sub_group) values ('a',2)

[RhinoBull]

I suppose you need a function that would return concatenated sub_group value for selected id.
So in your select sql you'd use function_name(id) instead of field name.

[wild_bill]

Here's one way using XML

declare @table table(id char(1), sub_group int)
insert into @table (id, sub_group) values ('a',1)
insert into @table (id, sub_group) values ('a',2)
insert into @table (id, sub_group) values ('b',1)
insert into @table (id, sub_group) values ('b',2)

select distinct t2.id,
	(select cast(t1.sub_group as varchar(3)) + ',' as [text()]
	from @table t1
	where t1.id = t2.id
	order by t1.sub_group
	for XML PATH(''))
from @table t2