Oracle에서 여러 Row를 하나의 컬럼으로 합치기

SQL을 작성하다 보면 여러 Row를 하나의 컬럼으로 합쳐서 보여줘야 할 경우가 있다.

MySQL에 group_concat 같은 개념이 Oracle에도 존재한다. 한 번 살펴보자.

Oracle의 경우 버전마다 할 수 있는 방식이 다르다. 먼저 언급하지만 버전이 올라갈수록 SQL이 간단해진다.

샘플 데이터

국가	차례	왕 이름
고구려	1대	동명왕
고구려	3대	대무신왕
백제	1대	온조왕
고구려	2대	유리왕
백제	3대	기루왕
신라	1대	남해왕
신라	1대	박혁거세
백제	2대	다루왕
신라	1대	유리이사금

• 샘플 데이터는 "여기"에서 가져왔다.

해결방식

1.9i에서 connect by + sys_connect_by_path 활용

-- 테스트 데이터 WITH TB_TEST AS (   SELECT '고구려' AS COUNTRY, '1대' AS ST, '동명성왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '고구려' AS COUNTRY, '3대' AS ST, '대무신왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '백제' AS COUNTRY, '1대' AS ST, '온조왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '고구려' AS COUNTRY, '2대' AS ST, '유리왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '백제' AS COUNTRY, '3대' AS ST, '기루왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '신라' AS COUNTRY, '1대' AS ST, '남해왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '신라' AS COUNTRY, '1대' AS ST, '박혁거세' AS KING_NM FROM DUAL   UNION ALL   SELECT '백제' AS COUNTRY, '2대' AS ST, '다루왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '신라' AS COUNTRY, '1대' AS ST, '유리이사금' AS KING_NM FROM DUAL ) --9i SELECT COUNTRY, SUBSTR(MAX(SYS_CONNECT_BY_PATH(KING_NM, ',')), 2) FROM (   SELECT COUNTRY, ST, KING_NM           , ROW_NUMBER() OVER (PARTITION BY COUNTRY ORDER BY ST) ST_NO   FROM TB_TEST ) START WITH ST_NO = 1 CONNECT BY PRIOR ST_NO = ST_NO - 1 AND PRIOR COUNTRY = COUNTRY GROUP BY COUNTRY ORDER BY COUNTRY ;

2.10g에서 xmlagg 활용

-- 테스트 데이터 WITH TB_TEST AS (   SELECT '고구려' AS COUNTRY, '1대' AS ST, '동명성왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '고구려' AS COUNTRY, '3대' AS ST, '대무신왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '백제' AS COUNTRY, '1대' AS ST, '온조왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '고구려' AS COUNTRY, '2대' AS ST, '유리왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '백제' AS COUNTRY, '3대' AS ST, '기루왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '신라' AS COUNTRY, '1대' AS ST, '남해왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '신라' AS COUNTRY, '1대' AS ST, '박혁거세' AS KING_NM FROM DUAL   UNION ALL   SELECT '백제' AS COUNTRY, '2대' AS ST, '다루왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '신라' AS COUNTRY, '1대' AS ST, '유리이사금' AS KING_NM FROM DUAL ) --10g SELECT COUNTRY,         SUBSTR(           XMLAGG(XMLELEMENT(COL, ',', KING_NM) ORDER BY ST)             .EXTRACT('//text()')             .GETSTRINGVAL()         , 2) KING_NMS FROM TB_TEST GROUP BY COUNTRY ;

3.10g에서 wm_concat 활용

-- 테스트 데이터 WITH TB_TEST AS (   SELECT '고구려' AS COUNTRY, '3대' AS ST, '대무신왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '고구려' AS COUNTRY, '1대' AS ST, '동명성왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '백제' AS COUNTRY, '1대' AS ST, '온조왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '고구려' AS COUNTRY, '2대' AS ST, '유리왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '백제' AS COUNTRY, '3대' AS ST, '기루왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '신라' AS COUNTRY, '1대' AS ST, '남해왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '신라' AS COUNTRY, '1대' AS ST, '박혁거세' AS KING_NM FROM DUAL   UNION ALL   SELECT '백제' AS COUNTRY, '2대' AS ST, '다루왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '신라' AS COUNTRY, '1대' AS ST, '유리이사금' AS KING_NM FROM DUAL ) --10g SELECT COUNTRY, WM_CONCAT(KING_NM) KING_NMS FROM (   SELECT COUNTRY, KING_NM   FROM TB_TEST   ORDER BY COUNTRY, ST ASC ) GROUP BY COUNTRY ;

4.11g에서 listagg 활용

-- 테스트 데이터 WITH TB_TEST AS (   SELECT '고구려' AS COUNTRY, '1대' AS ST, '동명성왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '고구려' AS COUNTRY, '3대' AS ST, '대무신왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '백제' AS COUNTRY, '1대' AS ST, '온조왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '고구려' AS COUNTRY, '2대' AS ST, '유리왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '백제' AS COUNTRY, '3대' AS ST, '기루왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '신라' AS COUNTRY, '1대' AS ST, '남해왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '신라' AS COUNTRY, '1대' AS ST, '박혁거세' AS KING_NM FROM DUAL   UNION ALL   SELECT '백제' AS COUNTRY, '2대' AS ST, '다루왕' AS KING_NM FROM DUAL   UNION ALL   SELECT '신라' AS COUNTRY, '1대' AS ST, '유리이사금' AS KING_NM FROM DUAL ) --11g SELECT COUNTRY, LISTAGG(KING_NM, '||') WITHIN GROUP (ORDER BY ST) KING_NMS FROM TB_TEST GROUP BY COUNTRY ;

각 방식마다 제약이 있는 경우가 있으나 11g를 사용한다면 좀 더 유연하고 쉽게 사용할 수 있다.

10g의 경우는 wm_concat 방식이 좀 더 쉬우나 xmlagg가 더 기능이 풍부하다(응용에 더 유연하다는 이야기)

버전에 맞게 적절히 사용하자. 만약 11g라면 listagg를 적극 권장한다.

P.S function을 사용하다보면 성능 문제를 우려하는 사람들이 있다. 다음 기획에 버전별 concat 기능에 대해 성능 체크를 한 번 해보자.

---

참고

• http://tyboss.tistory.com/entry/Oracle-XMLAGG-LISTAGG-%EC%82%AC%EC%9A%A9%ED%95%98%EA%B8%B0
• https://okky.kr/article/191834
• http://www.joshi.co.kr/index.php?document_srl=292615&mid=board_MfJr34