create table #tb(id int, value varchar(10),cname varchar(20))

insert into #tb values(1, 'aa','aaaa')

insert into #tb values(1, 'bb','eeee')

insert into #tb values(1, 'aa','tttt')

insert into #tb values(2, 'aaa','gggg')

insert into #tb values(2, 'bbb',null)

insert into #tb values(2, 'ccc','hhhh')

insert into #tb values(1, 'cc','llll')

go

----

---去重
select id, [value] = stuff((select distinct ',' + [value] from #tb t where id = #tb.id for xml path('')) , 1 , 1 , ''),max(isnull(cname,''))
from #tb
group by id
---不去重
select id, [value] = stuff((select ',' + [value] from #tb t where id = #tb.id for xml path('')) , 1 , 1 , ''),max(isnull(cname,''))
from #tb
group by id

drop table #tb