본문 바로가기
DB/Oracle

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

by Tomining 2017. 7. 7.
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 기능에 대해 성능 체크를 한 번 해보자.
참고

저작자표시 비영리

'DB > Oracle' 카테고리의 다른 글

ORA-03111 break received on communication channel  (0) 2016.07.15
Oracle lock session 처리하기  (0) 2016.07.14
ORA-01722: 수치가 부적합합니다  (0) 2016.07.01
Partition Range Iterator VS Partition Range ALL  (0) 2016.02.10
ORA-01861:literal does not match format string  (0) 2015.12.31

관련글

티스토리툴바