内容引自:
多行一列数据合并成一行一列数据
http://topic.csdn.net/u/20090714/17/5FE6A0F7-CE78-4936-BE31-21D462236059.html
在MySQL和Oracle中实现行合并
http://www.blogjava.net/rain1102/archive/2009/06/24/283867.html
SQL Server
--
SQL2005中的方法2
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, [ values ] = stuff (( select ' , ' + [ value ] from tb t where id = tb.id
for xml path( '' )), 1 , 1 , '' )
from tb
group by id
/*
id values
----------- --------------------
1 aa,bb
2 aaa,bbb,ccc
(2 row(s) affected)
*/
drop table tb
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, [ values ] = stuff (( select ' , ' + [ value ] from tb t where id = tb.id
for xml path( '' )), 1 , 1 , '' )
from tb
group by id
/*
id values
----------- --------------------
1 aa,bb
2 aaa,bbb,ccc
(2 row(s) affected)
*/
drop table tb
MySQL
select
name , group_concat(email
order
by
email separator ", ")
as
email
from
student
group
by
name
Oracle
如果以上效果想在Oracle中显示, 则比较复杂点了, 因为Oracle中没有行合并函数, 则需要使用sys_connect_by_path()来实现, 代码如下:
select name, ltrim (sys_connect_by_path(email, ' , ' ), ' , ' ) email from (
select name,email,
row_number() over (partition by name order by email) rn,
count ( * ) over (partition by name) cnt
from student
) where level = cnt
start with rn = 1
connect by prior name = name and prior rn + 1 = rn
select name, ltrim (sys_connect_by_path(email, ' , ' ), ' , ' ) email from (
select name,email,
row_number() over (partition by name order by email) rn,
count ( * ) over (partition by name) cnt
from student
) where level = cnt
start with rn = 1
connect by prior name = name and prior rn + 1 = rn