IT 개발

[오라클|Oracle] 조회값 연속으로 이어 붙이기 - SYS_CONNECT_BY_PATH

쭈니루 2013. 11. 9. 21:29
반응형

오라클 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  친가  고모,삼촌

 

이렇게 조회가 됩니다.

반응형