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

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

 

이렇게 조회가 됩니다.