Oracle中列转行的2种方法.

列转行

主要讨论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

 

3、ListAgg(Oracle 11g)

SQL> select deptno,

  2         listagg( ename, '; ' )

  3         within group

  4         (order by ename) enames

  5    from emp

  6   group by deptno

  7   order by deptno

  8  /

    DEPTNO ENAMES

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

        10 CLARK; KING; MILLER

        20 ADAMS; FORD; JONES;

           SCOTT; SMITH

        30 ALLEN; BLAKE;

           JAMES; MARTIN;

           TURNER; WARD