Oracle (11) - 여러 행의 데이터를 문자열로 합치는 방법 (LISTAGG)

데이터를 뽑는 업무를 하다 보면 종종 하나의 행에 모든 데이터를 추출하는 작업이 필요합니다. 

최근에 강의 평가 결과를 하나로 합해서 출력하는 작업을 진행하였고 이를 LISTAGG 함수는 이러한 작업을 수행하였습니다.

 

LISTAGG

SELECT COURSE	AS	강좌
     , LISTAGG('- ' || REVIEW, CHR(13)||CHR(10)) WITHIN GROUP (ORDER BY REVIEW) AS 강좌후기
  FROM COURSE_INFO
 GROUP BY COURSE;

해당 쿼리는 강좌후기를 강좌별로 나타내는 후기입니다. 

아래와 같이 데이터가 나옵니다.

강좌	강좌후기
강좌 A	- 매우 좋습니다. - 아주 좋습니다. - 좋습니다.
강좌 B	- 1. 해당 강좌는 만족스러웠다. - 2. 해당 강좌는 다소 아쉬웠다. - 3. 연강이라면 내년에 강좌를 들을 것이다.

COURSE(강좌) 기준으로 묶어줍니다. 앞에 있는 '- ' || REVIEW 를 통해 '- '을 접두사로 나오게 하였으며

CHR(13)||CHR(10)을 통하여 줄 바꿈을 하였습니다.

 

모든 데이터를 묶을 수 있을 것 같지만 4,000바이트를 넘기면 '문자열 연결의 결과가 너무 깁니다.'(ORA-01489) 에러를 뱉습니다. 해당 에러는 XMLAGG를 이용하면 해결할 수 있습니다.

 

XMLAGG

SELECT COURSE AS 강좌
     , SUBSTR(
           XMLAGG(
               XMLELEMENT(X, CHR(13)||CHR(10) || '- ' || REVIEW) 
               ORDER BY REVIEW
           ).EXTRACT('//text()').GETCLOBVAL()
       , 3) AS 강좌후기
  FROM COURSE_INFO
 GROUP BY COURSE;

4,000바이트를 넘기면 에러가 나오는 것은 VARCHAR 타입으로 데이터를 뽑기 때문에 그렇습니다. XMLAGG는 데이터를 XML 형태로 합하고 다시 CLOB로 뽑아내어 4,000바이트 제한 없이 무한으로 합할 수 있습니다.

 

여러 개의 형태로 합하였는데 각 함수의 역할은 다음과 같습니다.

• XMLELEMENT : XML 태그를 생성하면서 앞에 구분자인 줄바꿈 (CHR(13)||CHR(10))을 먼저 붙여줍니다.
• .EXTRACT('//text()') : 생성된 XML 데이터에서 실제 텍스트 데이터만 추출합니다.
• .GETCLOBVAL() : 추출한 데이터를 CLOB 타입으로 반환하여 4,000바이트 길이 제한을 회피합니다.
• SUBSTR(..., 3) : 모든 항목 앞에 줄바꿈 기호(CHR(13)||CHR(10))가 붙어서 합쳐지고, 맨 앞에 불필요하게 추가된 첫 번째 줄바꿈을 제거하기 위해 3번째 문자부터 자릅니다.

해당 방식을 이용하여 깔끔하게 데이터를 뽑을 수 있습니다.