- 描述:将如下形式的数据按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
一列多行值合并成一个值(MS SQL SERVER 2008)
最新推荐文章于 2024-05-25 04:00:00 发布