SYS_CONNECT_BY_PATH 用法

SQL> SELECT DEPTNO, SUBSTR(MAX(SYS_CONNECT_BY_PATH(ENAME, '/')), 2)  PATH
  2  FROM   (
  3         SELECT ENAME, DEPTNO, ROW_NUMBER() OVER (PARTITION BY DEPTNO  ORDER BY EMPNO) RN
  4         FROM   EMP
  5         )
  6  START WITH RN = 1
  7  CONNECT BY PRIOR RN = RN -1
  8         AND PRIOR DEPTNO = DEPTNO
  9  GROUP BY DEPTNO;
DEPTNO PATH
------ --------------------------------------------------------------------------------
    30 ALLEN/WARD/MARTIN/BLAKE/TURNER/JAMES
    20 SMITH/JONES/SCOTT/ADAMS/FORD
    10 CLARK/KING/MILLER

 

SQL> WITH T_10 AS
  2  (
  3  SELECT 1  AS A FROM DUAL
  4  UNION ALL
  5  SELECT 2  AS A FROM DUAL
  6  UNION ALL
  7  SELECT 3  AS A FROM DUAL
  8  UNION ALL
  9  SELECT 4  AS A FROM DUAL
 10  UNION ALL
 11  SELECT 5  AS A FROM DUAL
 12  UNION ALL
 13  SELECT 6  AS A FROM DUAL
 14  UNION ALL
 15  SELECT 7  AS A FROM DUAL
 16  UNION ALL
 17  SELECT 8  AS A FROM DUAL
 18  UNION ALL
 19  SELECT 9  AS A FROM DUAL
 20  UNION ALL
 21  SELECT 10  AS A FROM DUAL
 22  )
 23  SELECT SUBSTR(SYS_CONNECT_BY_PATH(A, ', '), 2)  PATH
 24  FROM   (
 25         SELECT ROWNUM RN, A FROM T_10
 26         )
 27  START WITH RN = 1
 28  CONNECT BY PRIOR RN = RN - 1;
PATH
--------------------------------------------------------------------------------
 1
 1, 2
 1, 2, 3
 1, 2, 3, 4
 1, 2, 3, 4, 5
 1, 2, 3, 4, 5, 6
 1, 2, 3, 4, 5, 6, 7
 1, 2, 3, 4, 5, 6, 7, 8
 1, 2, 3, 4, 5, 6, 7, 8, 9
 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
10 rows selected