ORACLE(오라클) LISTAGG 함수 정리

 

Oracle에서 문자열을 집계(aggregation)할 때 유용하게 사용하는 함수 중 하나가 바로 LISTAGG입니다. 주로 GROUP BY 결과를 문자열로 연결하고 싶을 때 사용하며, 실무에서 보고서, 로그 분석, 그룹별 항목 나열 등에 자주 활용됩니다.

---

1. LISTAGG 함수란?

LISTAGG는 집계 함수로, 그룹 내 다수의 행 값을 구분자를 사용하여 하나의 문자열로 결합해 줍니다.
Oracle 11g R2 (11.2)부터 지원되며, 버전별로 동작 방식에 차이가 있습니다.

---

2. 기본 문법

LISTAGG(컬럼명, '구분자') WITHIN GROUP (ORDER BY 정렬기준컬럼)

• 컬럼명: 연결할 문자열 컬럼
• '구분자': 각 값 사이에 넣을 구분자 (예: 쉼표 ,, 슬래시 / 등)
• WITHIN GROUP (ORDER BY ...): 정렬 기준 지정

---

3. 간단한 예제

📄 예제 테이블

CREATE TABLE EMPLOYEES (
  DEPT_ID NUMBER,
  EMP_NAME VARCHAR2(50)
);

INSERT INTO EMPLOYEES VALUES (10, 'Kim');
INSERT INTO EMPLOYEES VALUES (10, 'Lee');
INSERT INTO EMPLOYEES VALUES (10, 'Park');
INSERT INTO EMPLOYEES VALUES (20, 'Choi');
INSERT INTO EMPLOYEES VALUES (20, 'Jung');

부서별 직원 이름을 쉼표로 구분하여 나열

SELECT
  DEPT_ID,
  LISTAGG(EMP_NAME, ', ') WITHIN GROUP (ORDER BY EMP_NAME) AS EMP_LIST
FROM EMPLOYEES
GROUP BY DEPT_ID;

결과

DEPT_ID	EMP_LIST
10	Kim, Lee, Park
20	Choi, Jung

 

4. 중복 제거하고 싶을 땐?

Oracle의 LISTAGG는 기본적으로 중복 제거 기능이 없습니다. 따라서 중복을 제거하려면 서브쿼리 + DISTINCT 또는 COLLECT + CAST 방식과 조합해야 합니다.

SELECT
  DEPT_ID,
  LISTAGG(EMP_NAME, ', ') WITHIN GROUP (ORDER BY EMP_NAME) AS EMP_LIST
FROM (
  SELECT DISTINCT DEPT_ID, EMP_NAME
  FROM EMPLOYEES
)
GROUP BY DEPT_ID;

 

5. Oracle 12c 이상에서의 오류 방지 옵션

Oracle 11g에서는 LISTAGG 결과가 4000 bytes를 초과하면 ORA-01489: 문자열이 너무 깁니다 에러가 발생합니다.
Oracle 12.2 이상부터는 ON OVERFLOW 옵션으로 에러를 방지할 수 있습니다.

SELECT
  DEPT_ID,
  LISTAGG(EMP_NAME, ', ') WITHIN GROUP (ORDER BY EMP_NAME)
    ON OVERFLOW TRUNCATE '...' WITH COUNT
    AS EMP_LIST
FROM EMPLOYEES
GROUP BY DEPT_ID;

• TRUNCATE '...': 너무 길면 "..."으로 자름
• WITH COUNT: 몇 개 항목이 잘렸는지 표시

---

 

6. 역정렬이 필요한 경우?

WITHIN GROUP (ORDER BY ...) 안에서 DESC를 사용하면 됩니다.

LISTAGG(EMP_NAME, ', ') WITHIN GROUP (ORDER BY EMP_NAME DESC)

 

7. LISTAGG와 함께 자주 쓰는 패턴

다중 컬럼 결합

LISTAGG(EMP_NAME || '(' || DEPT_ID || ')', ', ') WITHIN GROUP (ORDER BY EMP_NAME)

 

8. LISTAGG 대안 함수 (Oracle 21c 이상)

Oracle 21c부터는 LISTAGG 대신 STRING_AGG 함수도 사용할 수 있습니다.

SELECT STRING_AGG(EMP_NAME, ', ') AS EMP_LIST
FROM EMPLOYEES;

STRING_AGG는 ANSI SQL 표준이며, 구문이 더 간단하지만 오직 Oracle 21c 이상에서만 사용 가능합니다.