oracle合并多行结果集,多行查询结果合并sys_connect_by_path

Oracle同行合并分组

表结构为：

create table test(

bookid char(3) not null,

author varchar2(10) not null

);

insert into test values('001','jack');

insert into test values('001','tom');

insert into test values('002','wang');

insert into test values('002','zhang');

insert into test values('002','li');

commit;

select * from test;

显示结果为：

BOO AUTHOR

-----------------

001 jack

001 tom

002 wang

002 zhang

002 li

我们想得到的结果为：

BOO AUTHOR

-----------------------------

001 jack&&tom

002 wang&&zhang&&li

SQL文为：

select bookid,substr(max(sys_connect_by_path(author,'&&')),3) author

from

(select bookid,author,id,lag(id) over(partition by bookid order by id) pid

--(最后一列或者为)lead(id) over(partition bybookid order by id desc) pid

from (select bookid,author,rownum id from test))

start with pid is null

connect by prior id=pid

group by bookid;

详细解释：

sys_connect_by_path(column,'')//column为列名，''中间加要添加的字符

这个函数本身不是用来给我们做结果集连接的(合并行)，而是用来构造树路径的，所以需要和connect by一起使用。