[RESOLVED] Concatonating data in SQL server 2005

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

Code:

---

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

---

Code:

---

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)

---

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.

Here's one way using XML

Code:

---

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

---