꿈꾸는 개발자, DBA 커뮤니티 구루비

문자추출+구분자+기존값합치기 질문입니다!

by 재래개 [SQL Query] [2016.05.22 08:32:05]

안녕하세요. 평화로운 주말의 아침입니다.

구루비 회원분들의 도움을 얻고자 이렇게 아침 일찍 문의를 드립니다..

2번 테이블 ITEM_FABRIC1 컬럼의 값 A100,B200,C300 을 문자만 추출(A,B,C)하여 3번 테이블의 FABRIC_NM으로(껍질,씨앗,잎) 바꾼 후

2번테이블의 문자 외의 값 100,200,300 을 3번 테이블로 변환한 값(껍질,씨앗,잎) + 기존값(100,200,300) -> 껍질100,씨앗200,잎300 으로 변환해야 합니다..

문자추출 후 3번테이블 조인해서 껍질,씨앗,잎 으로 바꾸는것까지는 되나 껍질100,씨앗200,잎300 으로 변환하는게 어렵습니다..

총 3개의 테이블이 있습니다.

1번과 2번 테이블의 KEY는 ITEM_CD(사과,감자,배) 입니다.

ITEM_CD	ITEM_SIZE	ITEM_QTY
사과	대	10
사과	중	9
사과	소	8
감자	대	10
감자	중	9
감자	소	8
딸기	대	10
딸기	중	9
딸기	소	8

 SELECT '사과' ITEM_CD, '대' ITEM_SIZE, '10' ITEM_QTY FROM DUAL UNION ALL
 SELECT '사과' ITEM_CD, '중' ITEM_SIZE, '9' ITEM_QTY FROM DUAL UNION ALL
 SELECT '사과' ITEM_CD, '소' ITEM_SIZE, '8'  ITEM_QTY FROM DUAL UNION ALL
 SELECT '감자' ITEM_CD, '대' ITEM_SIZE, '10' ITEM_QTY FROM DUAL UNION ALL
 SELECT '감자' ITEM_CD, '중' ITEM_SIZE, '9'  ITEM_QTY FROM DUAL UNION ALL
 SELECT '감자' ITEM_CD, '소' ITEM_SIZE, '8'  ITEM_QTY FROM DUAL UNION ALL
 SELECT '딸기' ITEM_CD, '대' ITEM_SIZE, '10' ITEM_QTY FROM DUAL UNION ALL
 SELECT '딸기' ITEM_CD, '중' ITEM_SIZE, '9'  ITEM_QTY FROM DUAL UNION ALL
 SELECT '딸기' ITEM_CD, '소' ITEM_SIZE, '8'  ITEM_QTY FROM DUAL ITEM

ITEM_CD	ITEM_FABRIC1	ITEM_FABRIC2
사과	A50,B30,C20	D100
감자	A50,B50	D50,E50
딸기	A10,B90	D10,E90

 SELECT '사과' ITEM_CD, 'A50,B30,C20' ITEM_FABRIC1, 'D100'    ITEM_FABRIC2 FROM DUAL UNION ALL
 SELECT '감자' ITEM_CD, 'A50,B50'     ITEM_FABRIC1, 'D50,E50' ITEM_FABRIC2 FROM DUAL UNION ALL
 SELECT '딸기' ITEM_CD, 'A30,B20,C50' ITEM_FABRIC1, 'D10,E90' ITEM_FABRIC2 FROM DUAL ITEM_FABRIC

FABRIC_CD	FABRIC_NM
A	껍질
B	씨앗
C	잎
D	단백질
E	비타민

 SELECT 'A' FABRIC_CD, '껍질'  FABRIC_NM FROM DUAL UNION ALL
 SELECT 'B' FABRIC_CD, '씨앗'  FABRIC_NM FROM DUAL UNION ALL
 SELECT 'C' FABRIC_CD, '잎'    FABRIC_NM FROM DUAL UNION ALL
 SELECT 'D' FABRIC_CD, '단백질' FABRIC_NM FROM DUAL UNION ALL
 SELECT 'E' FABRIC_CD, '비타민' FABRIC_NM FROM DUAL FABRIC

result :

ITEM_CD	ITEM_SIZE	ITEM_FABRIC1	ITEM_FABRIC2	ITEM_QTY
사과	대	껍질50,씨앗30,잎20	단백질100	10
사과	중	껍질50,씨앗30,잎20	단백질100	9
사과	소	껍질50,씨앗30,잎20	단백질100	8
감자	대	껍질50,씨앗50	단백질50,비타민50	10
감자	중	껍질50,씨앗50	단백질50,비타민50	9
감자	소	껍질50,씨앗50	단백질50,비타민50	8
딸기	대	껍질10,씨앗90	단백질10,비타민90	10
딸기	중	껍질10,씨앗90	단백질10,비타민90	9
딸기	소	껍질10,씨앗90	단백질10,비타민90	8

by jkson [2016.05.23 08:29:11]

with ITEM as
(
SELECT '사과' ITEM_CD, '대' ITEM_SIZE, '10' ITEM_QTY FROM DUAL UNION ALL
SELECT '사과' ITEM_CD, '중' ITEM_SIZE, '9' ITEM_QTY FROM DUAL UNION ALL
SELECT '사과' ITEM_CD, '소' ITEM_SIZE, '8'  ITEM_QTY FROM DUAL UNION ALL
SELECT '감자' ITEM_CD, '대' ITEM_SIZE, '10' ITEM_QTY FROM DUAL UNION ALL
SELECT '감자' ITEM_CD, '중' ITEM_SIZE, '9'  ITEM_QTY FROM DUAL UNION ALL
SELECT '감자' ITEM_CD, '소' ITEM_SIZE, '8'  ITEM_QTY FROM DUAL UNION ALL
SELECT '딸기' ITEM_CD, '대' ITEM_SIZE, '10' ITEM_QTY FROM DUAL UNION ALL
SELECT '딸기' ITEM_CD, '중' ITEM_SIZE, '9'  ITEM_QTY FROM DUAL UNION ALL
SELECT '딸기' ITEM_CD, '소' ITEM_SIZE, '8'  ITEM_QTY FROM DUAL 
)
, ITEM_FABRIC as
(
SELECT '사과' ITEM_CD, 'A50,B30,C20' ITEM_FABRIC1, 'D100'    ITEM_FABRIC2 FROM DUAL UNION ALL
SELECT '감자' ITEM_CD, 'A50,B50'     ITEM_FABRIC1, 'D50,E50' ITEM_FABRIC2 FROM DUAL UNION ALL
SELECT '딸기' ITEM_CD, 'A30,B20,C50' ITEM_FABRIC1, 'D10,E90' ITEM_FABRIC2 FROM DUAL 
)
, FABRIC as
(
SELECT 'A' FABRIC_CD, '껍질'  FABRIC_NM FROM DUAL UNION ALL
SELECT 'B' FABRIC_CD, '씨앗'  FABRIC_NM FROM DUAL UNION ALL
SELECT 'C' FABRIC_CD, '잎'    FABRIC_NM FROM DUAL UNION ALL
SELECT 'D' FABRIC_CD, '단백질' FABRIC_NM FROM DUAL UNION ALL
SELECT 'E' FABRIC_CD, '비타민' FABRIC_NM FROM DUAL 
)
SELECT B.ITEM_CD
     , B.ITEM_SIZE
     , A.ITEM_FABRIC1
     , A.ITEM_FABRIC2
     , B.ITEM_QTY
  FROM (SELECT ITEM_CD
             , LISTAGG(ITEM_FABRIC1, ',') WITHIN GROUP (ORDER BY LV) ITEM_FABRIC1
             , LISTAGG(ITEM_FABRIC2, ',') WITHIN GROUP (ORDER BY LV) ITEM_FABRIC2
          FROM (SELECT A.ITEM_CD
                     , B.FABRIC_NM || SUBSTR(ITEM_FABRIC1, 2) ITEM_FABRIC1
                     , C.FABRIC_NM || SUBSTR(ITEM_FABRIC2, 2) ITEM_FABRIC2
                     , LV LV
                  FROM (SELECT ITEM_CD
                             , REGEXP_SUBSTR(ITEM_FABRIC1, '[^,]+', 1, LV) ITEM_FABRIC1
                             , REGEXP_SUBSTR(ITEM_FABRIC2, '[^,]+', 1, LV) ITEM_FABRIC2
                             , LV
                          FROM ITEM_FABRIC A, (SELECT LEVEL LV
                                                 FROM DUAL
                                               CONNECT BY LEVEL < 20)
                         WHERE LV <= GREATEST(REGEXP_COUNT(ITEM_FABRIC1, ','), REGEXP_COUNT(ITEM_FABRIC1, ',')) + 1) A
                     , FABRIC B
                     , FABRIC C
                 WHERE SUBSTR(A.ITEM_FABRIC1, 1, 1) = B.FABRIC_CD(+)
                   AND SUBSTR(A.ITEM_FABRIC2, 1, 1) = C.FABRIC_CD(+))
        GROUP BY ITEM_CD) A
     , ITEM B
 WHERE B.ITEM_CD = A.ITEM_CD

만들고 보니 복잡...;;

by 재래개 [2016.05.23 10:22:44]

jkson 님 감사합니다!

by 우리집아찌 [2016.05.23 09:18:41]

WITH ITEM AS (
SELECT '사과' ITEM_CD, '대' ITEM_SIZE, '10' ITEM_QTY FROM DUAL UNION ALL
SELECT '사과' ITEM_CD, '중' ITEM_SIZE, '9' ITEM_QTY FROM DUAL UNION ALL
SELECT '사과' ITEM_CD, '소' ITEM_SIZE, '8'  ITEM_QTY FROM DUAL UNION ALL
SELECT '감자' ITEM_CD, '대' ITEM_SIZE, '10' ITEM_QTY FROM DUAL UNION ALL
SELECT '감자' ITEM_CD, '중' ITEM_SIZE, '9'  ITEM_QTY FROM DUAL UNION ALL
SELECT '감자' ITEM_CD, '소' ITEM_SIZE, '8'  ITEM_QTY FROM DUAL UNION ALL
SELECT '딸기' ITEM_CD, '대' ITEM_SIZE, '10' ITEM_QTY FROM DUAL UNION ALL
SELECT '딸기' ITEM_CD, '중' ITEM_SIZE, '9'  ITEM_QTY FROM DUAL UNION ALL
SELECT '딸기' ITEM_CD, '소' ITEM_SIZE, '8'  ITEM_QTY FROM DUAL 
) , ITEM_FABRIC AS (
SELECT '사과' ITEM_CD, 'A50,B30,C20' ITEM_FABRIC1, 'D100'    ITEM_FABRIC2 FROM DUAL UNION ALL
SELECT '감자' ITEM_CD, 'A50,B50'     ITEM_FABRIC1, 'D50,E50' ITEM_FABRIC2 FROM DUAL UNION ALL
SELECT '딸기' ITEM_CD, 'A30,B20,C50' ITEM_FABRIC1, 'D10,E90' ITEM_FABRIC2 FROM DUAL 
) , FABRIC AS (
SELECT 'A' FABRIC_CD, '껍질'  FABRIC_NM FROM DUAL UNION ALL
SELECT 'B' FABRIC_CD, '씨앗'  FABRIC_NM FROM DUAL UNION ALL
SELECT 'C' FABRIC_CD, '잎'    FABRIC_NM FROM DUAL UNION ALL
SELECT 'D' FABRIC_CD, '단백질' FABRIC_NM FROM DUAL UNION ALL
SELECT 'E' FABRIC_CD, '비타민' FABRIC_NM FROM DUAL 
) 

SELECT T1.ITEM_CD , T1.ITEM_SIZE , MAX(FABRIC1) FABRIC1 , MAX(FABRIC2) FABRIC2, T1.ITEM_QTY 
  FROM ITEM T1
     , ( SELECT ITEM_CD
             , GB
             , DECODE(GB,1,LISTAGG(VAL_NM, ',') WITHIN GROUP(ORDER BY LV)) FABRIC1 
             , DECODE(GB,2,LISTAGG(VAL_NM, ',') WITHIN GROUP(ORDER BY LV)) FABRIC2
          FROM
             (SELECT ITEM_CD 
                   , CASE WHEN SUBSTR(VAL,1,1) IN ( 'A', 'B' , 'C' ) THEN 1 ELSE 2 END GB
                   , REPLACE(VAL,B.FABRIC_CD,B.FABRIC_NM) VAL_NM
                   ,LV
                FROM (
                      SELECT ITEM_CD 
                            ,LV
                            ,REGEXP_SUBSTR(ITEM_FABRIC1||','||ITEM_FABRIC2,'[^,]+',1,LV) VAL
                        FROM ITEM_FABRIC AA
                          , (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 5 )           
                      ) A
                      , FABRIC B
              WHERE SUBSTR(VAL,1,1) = B.FABRIC_CD
             )
        GROUP BY ITEM_CD, GB
      ) T2
 WHERE T1.ITEM_CD = T2.ITEM_CD
 GROUP BY T1.ITEM_CD , T1.ITEM_SIZE , T1.ITEM_QTY 
 ORDER BY DECODE(T1.ITEM_CD,'사과',1,'감자',2,3) , T1.ITEM_SIZE

by 재래개 [2016.05.23 10:22:57]

우리집아찌 님 감사합니다!!

by 마농 [2016.05.23 09:32:47]

SELECT a.item_cd, a.item_size, a.item_qty
     , LISTAGG(
       REPLACE(REGEXP_SUBSTR(b.item_fabric1, c.fabric_cd||'[^,]+'), c.fabric_cd, c.fabric_nm)
       , ',') WITHIN GROUP(ORDER BY INSTR(b.item_fabric1, c.fabric_cd)) item_fabric1
     , LISTAGG(
       REPLACE(REGEXP_SUBSTR(b.item_fabric2, c.fabric_cd||'[^,]+'), c.fabric_cd, c.fabric_nm)
       , ',') WITHIN GROUP(ORDER BY INSTR(b.item_fabric2, c.fabric_cd)) item_fabric2
  FROM item        a
     , item_fabric b
     , fabric      c
 WHERE a.item_cd = b.item_cd
   AND INSTR(b.item_fabric1 || b.item_fabric2, c.fabric_cd) > 0
 GROUP BY a.item_cd, a.item_size, a.item_qty
 ORDER BY a.item_cd, INSTR('대중소', a.item_size)
;

by 재래개 [2016.05.23 10:23:09]

마농 님 감사합니다!!!

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변