if OBJECT_ID('tb') is not null drop table tb go create table tb(c1 int,c2 int) insert into tb select 1, 2 union all select 1, 3 union all select 1, 4 union all select 2, 2 union all select 2, 3 union all select 2, 5select c1,c2=STUFF((select ','+ltrim(c2) from tb where c1=a.c1 for xml path('')),1,1,'') from tb a group by c1c1 c2 1 2,3,4 2 2,3,5
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb(c1 int,c2 int)
insert into tb
select 1, 2 union all
select 1, 3 union all
select 1, 4 union all
select 2, 2 union all
select 2, 3 union all
select 2, 5select c1,c2=STUFF((select ','+ltrim(c2)
from tb
where c1=a.c1 for xml path('')),1,1,'')
from tb a
group by c1c1 c2
1 2,3,4
2 2,3,5
sql2005及以上的版本可以用2楼,sql2000不支持这种用法,只能用函数