안녕하세요. 아래와 같은 테이블이 있는데요.
해당 type과 qty로 구분되어져 있는데,
type의 순번과 qty의 순번의 matching되는 데이터입니다.
as-is 테이블을 to-be 테이블로 변경이 가능할까요?
TYPE컬럼에서 REGEXP_INSTR을 사용하여 QTY의 데이터를 가지고 나올려고 했으나,
해당자릿수로 Matching이 되지 않아, 고수님들의 도움을 받고자 합니다. ㅠㅠ
<as-is>
CATEGORY | TYPE | QTY |
FACTORY | AAA,BBB,CCC,DDD,EEE,FFF | 2,2,2,0,0,0 |
SCHOOL | AAA,CCC,EEE | 2,0,2 |
CHURCH | BBB,CCC,FFF | 2,0,1 |
HOUSE | CCC,DDD,EEE,FFF | 2,0,0,2 |
<to-be>
CATEGORY | AAA | BBB | CCC | DDD | EEE | FFF |
FACTORY | 2 | 2 | 2 | 0 | 0 | 0 |
SCHOOL | 2 | 0 | 2 | |||
CHURCH | 2 | 0 | 1 | |||
HOUSE | 2 | 0 | 0 | 2 |
WITH tbl (IDX,CATEGORY,TYPE,QTY) AS ( SELECT 1,'FACTORY','AAA,BBB,CCC,DDD,EEE,FFF','2,2,2,0,0,0' FROM DUAL UNION ALL SELECT 2,'SCHOOL' ,'AAA,CCC,EEE' ,'2,0,2' FROM DUAL UNION ALL SELECT 3,'CHURCH' ,'BBB,CCC,FFF' ,'2,0,1' FROM DUAL UNION ALL SELECT 4,'HOUSE' ,'CCC,DDD,EEE,FFF' ,'2,0,0,2' FROM DUAL ) SELECT CATEGORY, AAA, BBB, CCC,DDD, EEE, FFF FROM ( SELECT DISTINCT IDX, CATEGORY , REGEXP_SUBSTR(TYPE,'[^,]+', 1, LEVEL) TP , REGEXP_SUBSTR(QTY,'[^,]+', 1, LEVEL) QTY FROM tbl CONNECT BY REGEXP_SUBSTR(TYPE, '[^,]+', 1, LEVEL) IS NOT NULL ) AA PIVOT ( MIN(QTY) FOR TP IN ( 'AAA' AS AAA,'BBB' AS BBB,'CCC' AS CCC,'DDD' AS DDD,'EEE' AS EEE,'FFF' AS FFF ) ) ORDER BY IDX