오라클 여러 행을 하나의 컬럼으로 만들기 (LISTAGG) 오라클 11g R2 중복 제거

오라클에서는 여러 행으로 조회된 컬럼 값을 하나로 합쳐주는 함수가 있다.

오라클 9i 이하에서는 XMLAGG, 오라클 10g - 11g R1 에서는 WM_CONCAT, 오라클 11g R2 부터는 WM_CONCAT 함수를 사용할 수 없고, LISTAGG 함수를 사용하면 된다.

 

오라클 버전	함수명
~ 9i	XMLAGG
10g ~ 11g R1	WM_CONCAT
11g R2 ~	LISTAGG

 

LISTAGG 함수는 그룹 함수이므로 GROUP BY 또는 PARTITION BY 절과 함께 사용해야한다.

또한, WM_CONCAT 함수처럼 DISTINCT를 사용할 수 없지만 정규식 함수를 사용하여 중복 제거를 할 수 있고, ORDER BY절로 정렬도 가능하다.

 

기본 문법

SELECT LISTAGG(합칠 컬럼명, 구분자) WITHIN GROUP(ORDER BY 정렬 컬럼명)
  FROM DUAL

 

실습해보기

LISTAGG 함수를 사용해 보기 위해서 간단한 임시 테이블을 생성했다.

CREATE TABLE EMP (
      EMPNO INTEGER NOT NULL
    , ENAME VARCHAR(10)
);

INSERT INTO EMP VALUES(0, '홍길동');
INSERT INTO EMP VALUES(1, '홍길동');
INSERT INTO EMP VALUES(2, '홍길동');
INSERT INTO EMP VALUES(3, '홍길동');
INSERT INTO EMP VALUES(4, '두부');
INSERT INTO EMP VALUES(5, '두부');
INSERT INTO EMP VALUES(6, '김도기');
INSERT INTO EMP VALUES(7, '박지수');
INSERT INTO EMP VALUES(8, '정상미');
INSERT INTO EMP VALUES(9, '도민준');

 

 

▶ 하나의 컬럼 합치기

SELECT LISTAGG(ENAME, ',') WITHIN GROUP (ORDER BY EMPNO) AS ENAME
FROM EMP;

 

▶ 다른 컬럼과 함께 조회

다른 컬럼과 함께 조회하는 방법에는 두 가지가 있다 

GROUP BY 절을 사용하거나 PARTITION BY 절을 사용하는 것이다.

SELECT EMPNO
     , LISTAGG(ENAME, ',') WITHIN GROUP (ORDER BY EMPNO) AS ENAME
FROM EMP
GROUP BY EMPNO;

SELECT EMPNO
     , LISTAGG(ENAME, ',') WITHIN GROUP (ORDER BY EMPNO) OVER(PARTITION BY EMPNO) AS ENAME
FROM EMP

 

 

▶ 중복 제거

REGEXP_REPLACE 정규식 함수를 사용해서 컬럼의 중복을 제거할 수 있다. 

값이 순서대로 정렬되어 있어야 제대로 작동하므로 ORDER BY 절을 잘 볼 필요성이 있다.

SELECT REGEXP_REPLACE(LISTAGG(ENAME, ',') WITHIN GROUP (ORDER BY EMPNO), '([^,]+)(,\1)*(,|$)', '\1\3') AS ENAME
FROM EMP

 

아래와 같이 EMPNO: 10으로 '홍길동'을 추가했다. 

이후에 EMPNO 컬럼을 기준으로 정렬해서 조회했더니 정규식 함수가 제대로 먹지 않는 것을 볼 수 있다.

INSERT INTO EMP VALUES(10, '홍길동');

SELECT REGEXP_REPLACE(LISTAGG(ENAME, ',') WITHIN GROUP (ORDER BY EMPNO), '([^,]+)(,\1)*(,|$)', '\1\3') AS ENAME
FROM EMP

 

아래는 EMPNO 컬럼이 아닌 ENMAE 컬럼으로 정렬해서 조회했다.

중복되는 값이 순서대로 조회되어야 중복 제거가 제대로 이루어지는 것을 볼 수 있다.

SELECT REGEXP_REPLACE(LISTAGG(ENAME, ',') WITHIN GROUP (ORDER BY ENAME), '([^,]+)(,\1)*(,|$)', '\1\3') AS ENAME
FROM EMP