오라클 WM_CONCAT / LISTAGG 으로 여러 행을 한 행으로 합치는 방법

오라클에서 WM_CONCAT / LISTAGG를 사용해

여러 행의 내용을 한 행으로 합쳐줄 수 있는데

 

이미지에 사용한 예제들은 타이핑할 필요 없이

스크롤을 쭉 내리면

쿼리를 복사 붙여넣기 해서 사용해주면 된다

 

먼저 WM_CONCAT의 경우에는

WM_CONCAT(컬럼명)

을 사용해주면 테이블 여러 행의 값이

한 컬럼 안으로 들어온다

 

중복 제거를 위해서는

WM_CONCAT(DISTINCT 컬럼명)

으로 사용해주면 된다

 

WM_CONCAT의 경우에는 구분자가 ,로 고정이기 때문에

구분자를 콤마에서 다른 것으로 바꿔주려면

REPLACE(WM_CONCAT(컬럼명), ',', '구분자');

으로 사용해주면 된다

 

다음으로는 LISTAGG를 사용하는 경우인데

WM_CONCAT은 정렬해서 합치기가 불가능하고

구분자 변경 시 , 고정인데다가

합치려는 데이터에 ,가 들어있을 경우

replace도 사용하지 못하기 때문에 

 

이런 경우에는 LISTAGG를 사용해주면 된다

 

먼저 LISTAGG는

LISTAGG(컬럼명, '구분자') WITHIN GROUP(ORDER BY 컬럼명)

이렇게 사용하게 되는데

 

정렬하고 싶은 컬럼명이 없으면

해당 위치에 ROWNUM을 넣어주면 아무 정렬 조건 없이

SELECT를 했을때와 동일한 결과를 얻을 수 있다

 

다음으로 중복 제외의 경우에는

REGEXP_REPLACE에서 정규식을 사용해주면 되는데

REGEXP_REPLACE(LISTAGG(컬럼명, '구분자') WITHIN GROUP (ORDER BY 컬럼명), '([^,]+)(,\1)+', '\1')

 

위 내용을 복사 붙여넣기 해서

사용해주면 된다

 

마지막으로 값은 합치되

특정 값별로 분할해서 각 ROW에 합쳐주고 싶다면

PARTITION BY를 사용해주면 되는데

 

LISTAGG(컬럼명, '구분자') WITHIN GROUP(ORDER BY 컬럼명) OVER(PARTITION BY 컬럼명)

이렇게 사용해주면 된다

 

전체 예제에 사용한 쿼리는 아래 내용과 같다

 

-- WM_CONCAT(컬럼명) 예제
SELECT WM_CONCAT(JOB) AS WM_CC1 -- 여러 행을 한 행으로 합치기
       , WM_CONCAT(DISTINCT JOB) AS WM_CC2 -- 중복제거
       , REPLACE(WM_CONCAT(ENAME), ',', ' ') AS WM_CC3 -- 구분자 변경
  FROM EMP

-- LISTAGG(컬럼명, '구분자' WITHIN GROUP(ORDER BY 컬럼명)) 예제
SELECT LISTAGG(JOB, ' ') WITHIN GROUP(ORDER BY ROWNUM) AS LISTAGG1
       , REGEXP_REPLACE(LISTAGG(JOB, ' ') WITHIN GROUP (ORDER BY JOB), '([^,]+)(,\1)+', '\1') AS LISTAGG2
       -- ㄴ 중복제외
  FROM EMP 

-- LISTAGG PARTITION BY 사용할 경우(전체 행에 PARTITION BY 값 별로 묶어져서 들어감)
SELECT LISTAGG(JOB, ' ') WITHIN GROUP(ORDER BY ROWNUM) OVER(PARTITION BY JOB) AS LISTAGG1
  FROM EMP