--SQL2005中的方法
create table #tb(id int, value Nvarchar(100))
go
select * from #tb
declare @i int
set @i=0
while @i<100
begin
insert into #tb values(1,N'a你好a' +ltrim(str(@i)) )
insert into #tb values(1,N'b好b'+ltrim(str(@i)))
insert into #tb values(2,N'aaa'+ltrim(str(@i)))
insert into #tb values(2,N'cc好c' +ltrim(str(@i)))
insert into #tb values(2,N'ccc' +ltrim(str(@i)))
set @i=@i+1
end
select id, [values]=stuff((select ','+[value] from #tb t where id=#tb.id for xml path('')), 1, 1, '')
from #tb
group by id
GO
/*
id values
----------- --------------------
1 aa,bb
2 aaa,bbb,ccc
(2 row(s) affected)
*/
drop table #tb
