오라클 쿼리로 여러 행의 값 한 컬럼에 합치기

오늘은 오랜만에 오라클(?) 쿼리에 대해 기록을 남길까 합니다. 많이들 쓰긴 한데, 자주 쓸 일이 없어서 깜빡 잊고 있다가 필요할 때 찾게되는 쿼리라고나 할까요? 어느 기관에 데이터를 뽑아 제출해야 해서 급하게 찾아 사용했는데, 생각난 김에 정리해 볼까 합니다.

관계형 데이터베이스인 오라클에서 하나의 공통된 속성의 값을 합할 때 사용하는 쿼리를 작성하는 내용입니다.

예를 들어 OTC_PRODUCT_UNDERLYING 이라는 오라클 테이블에 이런 데이터가 있다고 합시다.

OTC_PRODUCT_NAME	UNDERLYING_ASSET
XX_ELS_공모_1055회	KOSPI200
XX_ELS_공모_1055회	NIKKEI225

이런 경우에 다음과 같이 출력해야 하는 상황입니다.

OTC_PRODUCT_NAME	UNDERLYING_ASSET
XX_ELS_공모_1055회	KOSPI200,NIKKEI225

최종적으로 나와야 하는 쿼리는 다음과 같습니다.

SELECT OTC_PRODUCT_NAME, SUBSTR(XMLAGG(XMLELEMENT(U_ASSET, ',' || UNDERLYING_ASSET) ORDER BY UNDERLYING_ASSET).EXTRACT('//TEXT()'), 2).getStringVal() AS UNDERLYING_ASSET

  FROM OTC_PRODUCT_UNDERLYING GROUP BY OTC_PRODUCT_NAME

자, 그러면 내부의 쿼리 항목을 하나씩 정리해 설명해 보겠습니다.

1. 우선 가장 먼저 XMLELEMENT 함수는 앞에서 지정한 태그(이 쿼리에서는 U_ASSET)로 컬럼의 값을 감싸줍니다.

SELECT OTC_PRODUCT_NAME, XMLELEMENT(U_ASSET, UNDERLYING_ASSET) AS UNDERLYING_ASSET

  FROM OTC_PRODUCT_UNDERLYING

이러면 최초의 테이블 데이터가 이렇게 변경되어 조회됩니다.

OTC_PRODUCT_NAME	UNDERLYING_ASSET
XX_ELS_공모_1055회	<U_ASSET>KOSPI200</U_ASSET>
XX_ELS_공모_1055회	<U_ASSET>NIKKEI225</U_ASSET>

2. 그 다음은 XML 값을 합쳐주는 XMLAGG 함수를 사용해 하나의 컬럼으로 모읍니다.

SELECT OTC_PRODUCT_NAME, XMLAGG(XMLELEMENT(U_ASSET, UNDERLYING_ASSET)) AS UNDERLYING_ASSET

  FROM OTC_PRODUCT_UNDERLYING GROUP BY OTC_PRODUCT_NAME

XMLAGG 함수를 사용하게 되면 테이블 데이터는 다음과 같은 형태로 변합니다. 한 컬럼으로 데이터가 모입니다.

OTC_PRODUCT_NAME	UNDERLYING_ASSET
XX_ELS_공모_1055회	<U_ASSET>KOSPI200</U_ASSET><U_ASSET>NIKKEI225</U_ASSET>

3. EXTRACT 함수를 사용해 XML 데이터에서 텍스트만 추출하고, 태그는 모두 지웁니다. 이 때 순서를 정렬해야 나중에 관리하기 편합니다. 예를 들어 어떤 데이터는 KOSPI200이 먼저 나오고, 어떤 데이터는 NIKKEI225가 먼저 나오는 것을 방지하는 차원의 작업이죠.

SELECT OTC_PRODUCT_NAME, XMLAGG(XMLELEMENT(U_ASSET, UNDERLYING_ASSET) ORDER BY UNDERLYING_ASSET).EXTRACT('//TEXT()') AS UNDERLYING_ASSET

  FROM OTC_PRODUCT_UNDERLYING GROUP BY OTC_PRODUCT_NAME

그러면 다음과 같이 데이터가 저장됩니다. 아, 정신이 없죠.

OTC_PRODUCT_NAME	UNDERLYING_ASSET
XX_ELS_공모_1055회	KOSPI200NIKKEI225

4. 다음으로는 UNERLYING_ASSET의 값을 구분해 줘야 합니다. 콤마(',')를 입력해 구분하도록 하겠습니다. 해놓고 나니 아까 처음 XML 태그에 구분이 필요할 때 이미 ','를 넣었더라면 싶습니다.

SELECT OTC_PRODUCT_NAME, XMLAGG(XMLELEMENT(U_ASSET, ',' || UNDERLYING_ASSET) ORDER BY UNDERLYING_ASSET).EXTRACT('//TEXT()') AS UNDERLYING_ASSET

  FROM OTC_PRODUCT_UNDERLYING GROUP BY OTC_PRODUCT_NAME

이 결과는 이렇게 저장됩니다. 첫번째 기초자산의 앞에도 콤마가 생깁니다.

OTC_PRODUCT_NAME	UNDERLYING_ASSET
XX_ELS_공모_1055회	,KOSPI200,NIKKEI225

5. 그러면 이제 기초자산 컬럼 맨 앞의 콤마를 지워줍니다. 이때 SUBSTR 함수를 사용합니다. 맨 앞의 2자리 이후의 데이터만 가져옵니다.

SELECT OTC_PRODUCT_NAME, SUBSTR(XMLAGG(XMLELEMENT(U_ASSET, ',' || UNDERLYING_ASSET) ORDER BY UNDERLYING_ASSET).EXTRACT('//TEXT()'), 2) AS UNDERLYING_ASSET

  FROM OTC_PRODUCT_UNDERLYING GROUP BY OTC_PRODUCT_NAME

이 쿼리의 결과만 본다면 작업이 다 된 것 같습니다만, 이 때 UNDERLYING_ASSET 컬럼의 타입은 XML 타입을 가집니다.

OTC_PRODUCT_NAME	UNDERLYING_ASSET
XX_ELS_공모_1055회	KOSPI200,NIKKEI225

6. 마지막으로 XML 타입을 문자열 타입으로 변경해 줍니다.(Optional) getStringVal() 함수를 사용하면 됩니다.

SELECT OTC_PRODUCT_NAME, SUBSTR(XMLAGG(XMLELEMENT(U_ASSET, ',' || UNDERLYING_ASSET) ORDER BY UNDERLYING_ASSET).EXTRACT('//TEXT()'), 2).getStringVal() AS UNDERLYING_ASSET

  FROM OTC_PRODUCT_UNDERLYING GROUP BY OTC_PRODUCT_NAME

다음에 또 써먹을 때 봐야겠습니다. 혹시 쿼리 해보시고 안되면 알려주세요. 지금 이 포스팅은 DB 접속 테스트 없이 했기 때문에 쿼리 괄호가 생략되는 등(-_-;) 일부가 부정확할 수 있습니다.