[ORACLE] LISTAGG()로 컬럼 값을 한 줄에 나열하는 방법

[ORACLE] LISTAGG

 

Oracle에서 제공하는 LISTAGG 함수는 행 데이터를 하나의 열 문자열로 집계하는 강력한 함수입니다. 본 글에서는 실무에서 자주 사용하는 다양한 예제와 함께 LISTAGG의 문법, 사용법, 오류 처리, 성능 팁 등을 자세히 다룹니다.

1. LISTAGG란 무엇인가?

LISTAGG는 Oracle 11g Release 2부터 지원되는 집계 함수(Aggregate Function)로, 특정 그룹의 문자열 데이터를 하나의 행으로 병합(concatenate)할 수 있게 해 줍니다.

📌 기본 문법

LISTAGG(column_name, 'separator') WITHIN GROUP (ORDER BY column_name)

✅ 간단 예제

SELECT department_id, 
       LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name) AS emp_list
  FROM employees
 GROUP BY department_id;

2. 정렬 기준 변경

ORDER BY 절은 출력 문자열의 순서를 지정할 수 있어 사용자 요구에 따라 유연하게 사용됩니다.

SELECT job_id, 
       LISTAGG(employee_name, '|') WITHIN GROUP (ORDER BY hire_date DESC) AS emp_by_hire
  FROM employees
 GROUP BY job_id;

3. 중복 제거 (DISTINCT)

Oracle 19c 이상부터 DISTINCT 옵션을 사용할 수 있습니다.

SELECT department_id, 
       LISTAGG(DISTINCT employee_name, ', ') WITHIN GROUP (ORDER BY employee_name)
  FROM employees
 GROUP BY department_id;

4. LISTAGG 한계와 ORA-01489 오류

LISTAGG는 기본적으로 VARCHAR2 최대 길이(4000 bytes)를 초과하면 다음과 같은 오류가 발생합니다.

오류: ORA-01489: result of string concatenation is too long

✔ 해결 방법 1: ON OVERFLOW TRUNCATE

SELECT department_id, 
       LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name)
       ON OVERFLOW TRUNCATE '...' 
  FROM employees
 GROUP BY department_id;

✔ 해결 방법 2: XMLAGG 함수 사용

SELECT department_id,
       RTRIM(XMLAGG(XMLELEMENT(e, employee_name || ', ').EXTRACT('//text()') ORDER BY employee_name)
         .GetClobVal(), ', ') AS emp_list
  FROM employees
 GROUP BY department_id;

5. 실무 응용 예제

✅ CSV 형식으로 출력

SELECT LISTAGG(employee_id || ',' || employee_name, '\n') 
         WITHIN GROUP (ORDER BY employee_id) AS csv_data
  FROM employees;

✅ REST API 응답 구조 구현

SELECT LISTAGG('"' || employee_name || '"', ',') 
         WITHIN GROUP (ORDER BY employee_name) AS json_names
  FROM employees;

✅ 보고서 헤더나 요약 정보 집계

SELECT department_id,
       LISTAGG(employee_name, ' / ') WITHIN GROUP (ORDER BY employee_name) AS summary
  FROM employees
 GROUP BY department_id;

6. Oracle 21c 이후 주요 개선사항

• ON OVERFLOW TRUNCATE WITH COUNT 도입
• IGNORE NULLS 지원 (기본값은 RESPECT NULLS)
• 성능 향상을 위한 내부 최적화

7. 성능 최적화 팁

• 정렬 칼럼에 인덱스를 부여하여 성능 개선
• LISTAGG 대신 XMLAGG 또는 JSON_ARRAYAGG 활용
• VARCHAR2 한계 고려: 문자열 길이 초과 시 CLOB 함수 대체

8. 요약

항목	내용
기능	행을 문자열로 병합
지원 버전	Oracle 11g R2 이상
오류 대응	OVERFLOW 옵션, XMLAGG 대체
활용 사례	CSV, 보고서 요약, JSON 응답 등

9. 참고 문헌 및 출처

• Oracle 공식 문서
• Oracle-Base: LISTAGG
• Stack Overflow: LISTAGG 태그