중복되어있는 주문번호별 상품코드를 나열하고자 한다.
방법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