LISTAGG 함수는 오라클 SQL에서 여러 행(row)의 값을 하나의 문자열로 합칠 때 사용하는 매우 유용한 집계 함수입니다. 특히 보고서나 로그 데이터, 태그 리스트 등에서 자주 사용되며, GROUP BY와 함께 자주 쓰입니다.
1. LISTAGG 함수 기본 사용법
SELECT LISTAGG(컬럼명, '구분자') WITHIN GROUP (ORDER BY 정렬기준)
FROM 테이블명;
예를 들어, 사원들이 속한 부서별로 이름을 쉼표로 구분하여 출력하고 싶다면 다음과 같이 사용할 수 있습니다.
SELECT deptno,
LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno;
2. WITHIN GROUP 절이 필요한 이유
LISTAGG는 집계함수이기 때문에 WITHIN GROUP 절을 사용하여 어떤 순서대로 문자열을 붙일지 지정해야 합니다. 만약 정렬 순서를 지정하지 않으면 오류가 발생합니다.
3. DISTINCT 적용 방법
기본적으로 LISTAGG는 중복된 값도 모두 합칩니다. 중복을 제거하고 싶다면 DISTINCT 키워드를 사용할 수 있습니다. (오라클 19c 이상 지원)
SELECT deptno,
LISTAGG(DISTINCT ename, ', ') WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno;
4. LISTAGG 오류 해결법 (ORA-01489)
ORA-01489: result of string concatenation is too long 에러는 LISTAGG 결과가 VARCHAR2 4000 BYTE 제한을 넘어서면 발생합니다.
해결 방법
대상 컬럼을 줄이거나 필터로 제한
LISTAGG 대신 XMLAGG 또는 STRING_AGG (21c 이상) 사용
-- XMLAGG 대체 방법
SELECT deptno,
RTRIM(XMLAGG(XMLELEMENT(e, ename || ',').EXTRACT('//text()') ORDER BY ename).GetClobVal(), ',') AS employees
FROM emp
GROUP BY deptno;
5. 오라클 21c부터는 STRING_AGG도 가능
오라클 21c부터는 STRING_AGG 함수도 지원합니다. 이 함수는 LISTAGG보다 유연하며 4000 byte 한계가 없습니다.
SELECT deptno,
STRING_AGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno;
6. LISTAGG 실무 활용 예시
1) 고객별 구매상품 리스트
SELECT customer_id,
LISTAGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name) AS product_list
FROM orders
GROUP BY customer_id;
2) 오류코드별 발생 시스템 리스트
SELECT error_code,
LISTAGG(system_name, ', ') WITHIN GROUP (ORDER BY system_name) AS affected_systems
FROM error_log
GROUP BY error_code;
