ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle9i中使用SYS_CONNECT_BY_PATH进行行列转换-

Oracle9i中使用SYS_CONNECT_BY_PATH进行行列转换-

原创 Linux操作系统 作者:star_guan2008 时间:2008-03-29 10:25:55 0 删除 编辑
有表:

SQL> SELECT deptno, ename FROM emp ORDER BY deptno, ename;

DEPTNO ENAME
------ ----------
    10 CLARK
    10 KING
    10 MILLER
    20 ADAMS
    20 FORD
    20 JONES
    20 SCOTT
    20 SMITH
    30 ALLEN
    30 BLAKE
    30 JAMES
    30 MARTIN
    30 TURNER
    30 WARD

14 rows selected.

想输出为:

DEPTNO ENAME
------ ----------
    10 CLARK, KING, MILLER
    20 ADAMS, FORD, JONES, SCOTT, SMITH
    30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

 

除了使用聚集函数或者存储过程之外,9i中可以:

SQL> SELECT deptno
  2       , LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
  3         KEEP (DENSE_RANK LAST ORDER BY curr),',') AS concatenated
  4  FROM   ( SELECT deptno
  5                , ename
  6                , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr
  7                , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
  8           FROM   emp )
  9  GROUP BY deptno
 10  CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
 11  START WITH curr = 1;

    DEPTNO
----------
CONCATENATED
----------------------------------------------------------------------------------------------------
        10
CLARK,KING,MILLER

        20
ADAMS,FORD,JONES,SCOTT,SMITH

        30
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10742223/viewspace-219148/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-02-19

  • 博文量
    82
  • 访问量
    107172