오라클 여러 행(ROW)을 하나의 컬럼(COLUMN)으로 합치기

오라클에서 GROUP BY 절과 SUM 함수를 사용하여 숫자를 합산할 수 있다. 그러나 문자열인 경우 SUM 함수를 사용하여 문자열을 합칠 수는 없고 대신에 LISTAGG, XMLAGG, WM_CONCAT 함수를 사용하여 그룹별 문자열 칼럼의 값을 하나의 칼럼으로 합칠 수 있다. 문자열을 합치는 함수는 오라클 버전마다 조금씩 사용법이 다르므로 아래의 예제를 참고하여 상황에 맞는 방법을 사용하면 된다.

 

목차 LISTAGG 함수를 사용하여 문자열 합치기 (Oracle 11g R2 이상) XMLAGG 함수를 사용하여 문자열 합치기 (Oracle 9i 이상) WM_CONCAT 함수를 사용하여 문자열 합치기 (Oracle 10g ~ 11g R1)

 

LISTAGG 함수를 사용하여 문자열 합치기 (Oracle 11g R2 이상)

SELECT job
     , LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS enames
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')
 GROUP BY job

 

 

오라클 11g R2 이상에서 LISTAGG 함수를 사용하여 그룹별 문자열을 합칠 수 있다.

 

ORDER BY 절을 사용하여 문자열을 정렬하여 합칠 수 있다. 오라클 19C 이상 버전에서는 DISTINCT를 사용하여 문자열 중복을 제거할 수 있지만, 이전 버전에서는 DISTINCT를 사용할 수 없기 때문에 정규식 함수를 사용하여 문자열 중복을 제거한다. (포스팅 하단의 LISTAGG 함수 사용법 링크 참조)

 

XMLAGG 함수를 사용하여 문자열 합치기 (Oracle 9i 이상)

SELECT job
     , SUBSTR(XMLAGG(XMLELEMENT(nm, ',', ename) ORDER BY ename).EXTRACT('//text()').GETSTRINGVAL(), 2) AS enames
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')
 GROUP BY job

 

LISTAGG 함수를 사용할 수 없는 오라클 버전에서는 주로 XMLAGG 함수를 사용하여 문자열을 합친다. 구문이 조금 복잡해 보이지만 기능은 아주 잘 작동한다. ORDER BY 절을 사용하여 문자열을 정렬 후 문자열을 합칠 수 있다.

 

WM_CONCAT 함수를 사용하여 문자열 합치기 (Oracle 10g ~ 11g R1)

SELECT job
     , WM_CONCAT(ename) AS enames
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')
 GROUP BY job

 

WM_CONCAT 함수는 오라클 10g 버전부터 11g R1 버전까지 사용할 수 있다. 해당 버전이 아니면 오류가 발생한다. (ORA-00904: "WM_CONCAT": 부적합한 식별자)

 

특정 오라클 버전에서 한정적으로 사용할 수 있으므로 사용을 권장하지 않지만, 간단한 쿼리나 데이터  추출할 때 자주 사용한다. ORDER BY 절을 사용하여 합칠 문자열을 정렬할 수는 없으나 DISTINCT를 사용할 수 있기 때문에 아주 유용하다. (예, WM_CONCAT(DISTINCT ename)  AS enames )

 

출처: https://gent.tistory.com/550