oracle 컬럼 가로 표시 / xmlagg 특수문자 치환 / WM_CONCAT / Listagg

oracle 컬럼 가로 표시 / xmlagg 특수문자 치환 / WM_CONCAT / Listagg

 

 

 

쿼리를 조회하다 보면 컬럼을 가로로 표시해야 할 경우가 생긴다.

 

oracle 10g 버전에서는 XMLAGG, WM_CONCAT 함수를 사용가능하며

 

oracle 11g 버전에서는 LISTAGG 함수를 사용가능하다.

 

WM_CONCAT, LISTAGG 함수의 사용방법은 아래 주소(링크)를 참고하면 된다.

 

oracle / 컬럼의 값을 가로로 표시하기 / WM_CONCAT / Listagg / 옆으로 표시

 

 

 

 

 

필자는 oracle 10g 에서 XMLAGG 를 사용하여 컬럼을 가로로 표시할 경우가 생겼다.

 

 

 

XMLAGG 의 기본적인 사용방법은 다음과 같다.

 

XMLAGG (XMLELEMENT (태그로사용할문자, 구분자, 가로로 표시할 컬럼) ORDER BY 정렬할컬럼).EXTRACT ('//text()')

 

먼저 가장 안쪽에 XMLELEMENT 함수는 가로로 표시할 컬럼을 태그로 감싸고 태그 안에 구분자를 추가한다.

예를 들면 다음과 같은 모양이다.

    SELECT XMLELEMENT (X, '|', 'abcd')
    FROM DUAL

결과 : <X>|abcd</X>

 

그리고 난 후 XMLAGG를 사용하여 xml 값을 order by 기준에 맞춰서 나란히 붙힌다.

결과 예 : <X>|abcd</X><X>|efgh</X>

 

그 다음 EXTRACT 함수를 사용하여 태그를 제외한 text만 추출한다.

결과 예 : |abcd|efgh

 

 

 

이해를 돕기위해 다음과 같은 데이터를 DUAL 테이블을 사용하여 만들었다.

 

        SELECT
            'abcd & > abcd' AS textVal,
            '1' AS textOrder
        FROM DUAL
        UNION ALL
        SELECT 
            'efg & > efg' AS textVal,
            '2' AS textOrder
        FROM DUAL

 

실행하면 다음과 같은 데이터를 만들 수 있다.

 

 

이제 textval 이라는 컬럼을 가로로 표시해 보자.

SELECT SUBSTR (
          XMLAGG (XMLELEMENT (x, ' ', textVal) ORDER BY textOrder).EXTRACT (
             '//text()').getstringval(),
          2) as textVal
FROM (
        SELECT
            'abcd & > abcd' AS textVal,
            '1' AS textOrder
        FROM DUAL
        UNION ALL
        SELECT 
            'efg & > efg' AS textVal,
            '2' AS textOrder
        FROM DUAL
     )

위 쿼리를 실행해 보자.

XMLELEMENT 함수로 각 컬럼을 xml로 치환하고

XMLAGG 를 사용하여 textOrder 기준으로 정렬하여 컬럼을 하나로 합쳤다.

EXTRACT 함수로 xml을 제외하고 text를 추출했고

한글이 깨지는 것에 대비하여 getstringval 함수를 사용해서 값을 string로 변환하여 사용했다.

영문과 숫자만 있다면 굳이 getstringval 함수를 사용할 필요는 없다.

substr을 사용하여 첫글자를 제외한 2번째 글자부터 출력하였다.(가장 처음에 있는 구분자를 제외하기 위해)

 

 

그랬더니 다음과 같은 결과가 나왔다.

 

& 기호가 & 로 > 기호가 > 로 치환되어있었다.

 

찾아보니 XMLAGG 를 사용하면 일부 특수문자가 표시되지 않는 단점이 있다고 한다.

 

즉 위의 쿼리는 특수문자가 없는 데이터라면 사용해도 상관없다.

 

하지만 특수문자가 있다면 당연히 사용해서는 안된다.

 

이를 해결하려고 XMLAGG 를 사용한 데이터에 getstringval 함수는 제외하고

EXTRACTVALUE, XMLELEMENT 를 사용하여 다시한번 치환하였다.

getstringval 함수를 제외해도 한글을 출력하는데 문제가 없었다.

SELECT SUBSTR (
          EXTRACTVALUE (
             XMLELEMENT (
                x,
                XMLAGG (XMLELEMENT (x, ' ', textVal) ORDER BY textOrder).EXTRACT (
                   '//text()')),
             '//text()'),
          2) as textVal
FROM (
        SELECT
            'abcd & > abcd' AS textVal,
            '1' AS textOrder
        FROM DUAL
        UNION ALL
        SELECT 
            'efg & > efg' AS textVal,
            '2' AS textOrder
        FROM DUAL
     )

 

이제 특수문자도 정상적으로 출력되는 것을 알 수 있다.