[Oracle] LISTAGG 여러 행을 하나의 컬럼에 가져오기

Oracle은 여러 행의 데이터를 하나의 컬럼으로 가져 올 때 쓸 수 있는 유용한 함수가 있는데요.

 오늘 소개할 함수는 LISTAGG  함수 입니다.

 

  버전 주의사항  

LISTAGG 는 Oracle 11g 에서 추가된 함수 입니다.

10g~11g R1 은  WM_CONCAT 함수를 사용해 주세요.

Oracle 9i 이하의 경우는 XMLAGG 함수를 쓰시면 됩니다.

 

  함께 사용  

LISTAGG() 함수는 그룹함수입니다.

그래서 GROUP BY 혹은 PARTITION BY 절과 함께 사용해줘야 합니다.

DISTINCT 사용 여부는 WM_CONCAT 함수와는 가능하지만,

LISTAGG에서는 사용 불가 합니다. 

대신 정규식 함수를 사용해 중복을 제거할 수 있습니다.

ORDER BY절을 사용한 값의 정렬도 가능 합니다.

 

LISTAGG 문법

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

 

예시

	LAN
1	루비
2	루비
3	루비
4	파이선
5	자바스크립트
6	자바

조회 했을 대 이런 데이터가 있다고 가정 해봅니다.

루비는 중복 데이터, 나머지는 단일 데이터입니다.

 

LISTAGG 하나의 열로 

SELECT LISTAGG(LAN, ',') WITHIN GROUP(ORDER BY LAN) AS LAN
  FROM LAN_INFO

	LAN
1	루비,루비,루비,자바,자바스크립트,파이선

여러 행이의 데이터들이 하나의 행으로 합쳐져서 표시 됩니다.

구분자로 넣었단 ',' 콤마가 LAN 데이터 사이 사이에 들어가서 구분 되고 ORDER BY로 LAN이 정렬 됩니다.

 

중복 제거 + 하나의 열로 합치기

SELECT REGEXP_REPLACE(LISTAGG(LAN, ',') WITHIN GROUP(ORDER BY LAN) OVER(PARTITION BY LAN), '([^,]+)(,\1)*(,|$)', '\1\3') AS LAN
  FROM LAN_INFO

	LAN
1	루비,자바,자바스크립트,파이선

정규식 함수 REGEXP_REPLACE를 쓰면 DISTINCT 처럼 중복을 제거할 수 있습니다.

하지만 값의 순서가 꼭 정렬되어 있어야 정확하게 중복이 제거 됩니다~!

 

 

<참조사이트>

https://js2prince.tistory.com/entry/Oracle-LISTAGG-WITHIN-GROUP

https://gent.tistory.com/328