[SQL] 쿼리로 데이터 전처리하기

아래와 같이 중복되어있는 주문번호별 상품코드를 나열하고자 한다.

방법1. (SUBSTR(XMLAGG(XMLELEMENT)))

CREATE TABLE MYCDAssociation
 AS SELECT ORDERNUM
     , SUBSTR(XMLAGG(XMLELEMENT(COL ,',', PRODUCTCODE)).EXTRACT('//text()').GETSTRINGVAL(), 2) PRODUCTCODE
     FROM mycdprod GROUP BY ORDERNUM

방법2. (WM_CONCAT)

SELECT ORDERNUM
     , WM_CONCAT(PRODUCTCODE) AS GB
  FROM MYCDPROD
 GROUP BY ORDERNUM
 HAVING COUNT(*) > 1
 ;

💡 cnt개수 최댓값 구하는 방법(cnt=위 오른쪽 데이터의 ,로 구분된 데이터의 개수)

SELECT MAX(CNT) FROM  (
 SELECT ORDERNUM
     , WM_CONCAT(PRODUCTCODE) AS GB, COUNT(*) CNT
  FROM MYCDPROD
 GROUP BY ORDERNUM)

• 콤마를 가로로 분리하는 작업

CREATE TABLE SELECT
     REGEXP_SUBSTR(productcode, '[^,]+', 1,1 ) AS a
   , REGEXP_SUBSTR(productcode, '[^,]+', 1,2 ) AS s
   , REGEXP_SUBSTR(productcode, '[^,]+', 1,3 ) AS d
   , REGEXP_SUBSTR(productcode, '[^,]+', 1,4 ) AS f
   , REGEXP_SUBSTR(productcode, '[^,]+', 1,5 ) AS g
   , REGEXP_SUBSTR(productcode, '[^,]+', 1,6 ) AS h
   , REGEXP_SUBSTR(productcode, '[^,]+', 1,7 ) AS j
   , REGEXP_SUBSTR(productcode, '[^,]+', 1,8 ) AS k
   , REGEXP_SUBSTR(productcode, '[^,]+', 1,9 ) AS l
   , REGEXP_SUBSTR(productcode, '[^,]+', 1,10 ) AS q
   , REGEXP_SUBSTR(productcode, '[^,]+', 1,11 ) AS w
   , REGEXP_SUBSTR(productcode, '[^,]+', 1,12 ) AS e
   , REGEXP_SUBSTR(productcode, '[^,]+', 1,13 ) AS r
FROM mycdassociation