오라클 Oracle SYS_CONNECT_BY_PATH


조회된 값을 이어 붙이고 싶으실 때는 SYS_CONNECT_BY_PATH 을 사용하시면 됩니다.

 

 

 SELECT SUBSTR(MAX(SYS_CONNECT_BY_PATH(A, ',')), 2) PATH_1,
   SUBSTR(MAX(SYS_CONNECT_BY_PATH(B, ',')), 2) PATH_2
 FROM (
    SELECT ROWNUM RNUM,
      A,
      B
    FROM (
        SELECT '가족' A, '아버지' B FROM DUAL
        UNION
        SELECT '가족' A, '어머니' B FROM DUAL
        UNION
        SELECT '가족' A, '형' B FROM DUAL
        UNION
        SELECT '가족' A, '누나' B FROM DUAL
        UNION
        SELECT '친가' A, '삼촌' B FROM DUAL
        UNION
        SELECT '친가' A, '고모' B FROM DUAL
        UNION
        SELECT '외가' A, '외삼촌' B FROM DUAL
        UNION
        SELECT '외가' A, '이모' B FROM DUAL
       ORDER BY A, B)) START WITH RNUM = 1 CONNECT BY PRIOR RNUM = RNUM - 1

 

 

 

 PATH_1

 PATH_2

 1

 가족,가족,가족,가족,외가,외가,친가,친가

 누나,아버지,어머니,형,외삼촌,이모,고모,삼촌

 

혹시, GROUP 별로 값을 나열하고 싶으시다면,

 

 

SELECT A ,
   SUBSTR(MAX(SYS_CONNECT_BY_PATH(B, ',')), 2) PATH
 FROM (
    
SELECT ROW_NUMBER() OVER(PARTITION BY SUBSTR(A, 1, 1) ORDER BY A, B) RNUM, -- A GROUP 별로 ROWNUM 지정
      A,
      B
    FROM (
        SELECT '가족' A, '아버지' B FROM DUAL
        UNION
        SELECT '가족' A, '어머니' B FROM DUAL
        UNION
        SELECT '가족' A, '형' B FROM DUAL
        UNION
        SELECT '가족' A, '누나' B FROM DUAL
        UNION
        SELECT '친가' A, '삼촌' B FROM DUAL
        UNION
        SELECT '친가' A, '고모' B FROM DUAL
        UNION
        SELECT '외가' A, '외삼촌' B FROM DUAL
        UNION
        SELECT '외가' A, '이모' B FROM DUAL
        ORDER BY A, B) ) START WITH RNUM = 1 CONNECT BY PRIOR RNUM = RNUM - 1
 
AND PRIOR A = A  -- A 값이 같은 넘들끼리
 GROUP BY A
 ORDER BY A

 

 

하시면,

 

 

 A

 PATH

 1

 가족

 누나,아버지,어머니,형

 2

 외가

 외삼촌,이모

 3

 친가

 고모,삼촌

 

이렇게 조회가 됩니다.

 

 

Posted by 쭈니루
TAG

댓글을 달아 주세요