本文主要讨论sys_connect_by_path的使用方法。
1、带层次关系
SQL> create table dept(deptno number,deptname varchar2(20),mgrno number);
Table created.
SQL> insert into dept values(1,'总公司',null);
1 row created.
SQL> insert into dept values(2,'浙江分公司',1);
1 row created.
SQL> insert into dept values(3,'杭州分公司',2);
1 row created.
SQL> commit;
Commit complete.
SQL> select max(substr(sys_connect_by_path(deptname,','),2)) from dept connect by prior deptno=mgrno;
MAX(SUBSTR(SYS_CONNECT_BY_PATH(DEPTNAME,','),2))
--------------------------------------------------------------------------------
总公司,浙江分公司,杭州分公司
2、行列转换
如把一个表的所有列连成一行，用逗号分隔:
SQL> select max(substr(sys_connect_by_path(column_name,','),2))
from (select column_name,rownum rn from user_tab_columns where table_name ='DEPT')
start with rn=1 connect by rn=rownum ;
MAX(SUBSTR(SYS_CONNECT_BY_PATH(COLUMN_NAME,','),2))
--------------------------------------------------------------------------------
DEPTNO,DEPTNAME,MGRNO
