SQL 多行值变成一个单元格值

前面需求没看懂,后面是个合并字符串. SQL code /* 标题：按某字段合并字符串之一(简单合并) 作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 时间：2008-11-06 地点：广东深圳 描述：将如下形式的数据按id字段合并value字段。 id value ----- ------ 1 aa 1 bb 2 aaa 2 bbb 2 ccc 需要得到结果： id value ------ ----------- 1 aa,bb 2 aaa,bbb,ccc 即：group by id, 求 value 的和（字符串相加） */ -- 1、sql2000中只能用自定义的函数解决 create table tb(id int , value varchar ( 10 )) insert into tb values ( 1 , ' aa ' ) insert into tb values ( 1 , ' bb ' ) insert into tb values ( 2 , ' aaa ' ) insert into tb values ( 2 , ' bbb ' ) insert into tb values ( 2 , ' ccc ' ) go create function dbo.f_str( @id varchar ( 10 )) returns varchar ( 1000 ) as begin declare @str varchar ( 1000 ) select @str = isnull ( @str + ' , ' , '' ) + cast (value as varchar ) from tb where id = @id return @str end go -- 调用函数 select id , value = dbo.f_str(id) from tb group by id drop function dbo.f_str drop table tb -- 2、sql2005中的方法 create table tb(id int , value varchar ( 10 )) insert into tb values ( 1 , ' aa ' ) insert into tb values ( 1 , ' bb ' ) insert into tb values ( 2 , ' aaa ' ) insert into tb values ( 2 , ' bbb ' ) insert into tb values ( 2 , ' ccc ' ) go select id, [ value ] = stuff (( select ' , ' + [ value ] from tb t where id = tb.id for xml path( '' )) , 1 , 1 , '' ) from tb group by id drop table tb -- 3、使用游标合并数据 create table tb(id int , value varchar ( 10 )) insert into tb values ( 1 , ' aa ' ) insert into tb values ( 1 , ' bb ' ) insert into tb values ( 2 , ' aaa ' ) insert into tb values ( 2 , ' bbb ' ) insert into tb values ( 2 , ' ccc ' ) go declare @t table (id int ,value varchar ( 100 )) -- 定义结果集表变量 -- 定义游标并进行合并处理 declare my_cursor cursor local for select id , value from tb declare @id_old int , @id int , @value varchar ( 10 ) , @s varchar ( 100 ) open my_cursor fetch my_cursor into @id , @value select @id_old = @id , @s = '' while @@FETCH_STATUS = 0 begin if @id = @id_old select @s = @s + ' , ' + cast ( @value as varchar ) else begin insert @t values ( @id_old , stuff ( @s , 1 , 1 , '' )) select @s = ' , ' + cast ( @value as varchar ) , @id_old = @id end fetch my_cursor into @id , @value END insert @t values ( @id_old , stuff ( @s , 1 , 1 , '' )) close my_cursor deallocate my_cursor select * from @t drop table tb