文章转自http://www.cnblogs.com/yongheng178/archive/2012/06/27/2565631.html
今日看到一篇文章,是关于和并列的,也研究了下,还是不错的
要这种效果。
create table tb(idint, value varchar(10))
insert into tbvalues(1,'aa')
insert into tbvalues(1,'bb')
insert into tbvalues(2,'aaa')
insert into tbvalues(2,'bbb')
insert into tbvalues(2,'ccc')
go
/* stuff(param1, startIndex, length, param2)
说明:将param1中自startIndex(SQL中都是从1开始,而非0)起,删除length个字符,然后用param2替换删掉的字符。*/
SELECT id,
value = stuff
((SELECT ',' + value
FROM tb AS t
WHERE t .id = tb.id FOR xml path('')), 1, 1, '')
FROM tb
GROUP BY id
这样即可。
===================================================================================
收集的资料
- /*
- 标题:按某字段合并字符串之一(简单合并)
- 作者:(十八年风雨,守得冰山雪莲花开)
- 时间: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