串欄位list新招，不用建ufn.

 

 

--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