오라클 split 질문 ㅜㅜ 0 2 1,260

by 몽몽이 [2019.02.08 13:57:02]


mybatis 사용중이며

현재 데이터가 코드로 1~15 까지 한 컬럼에 ex) 1,7,12 같은 식으로 들어있습니다.

그런데 이 부분을 쿼리에서 1이면 사과 7이면 배 이런식으로 나오게 해야하는데 어떻게 해야할까요 ㅠㅠ 

replace로 쓰려고했는데 1 바꾸면 10~15까지 다 바뀌어버리네요

by 우리집아찌 [2019.02.08 14:17:53]
WITH T (SEQ , TXT) AS ( 
SELECT 1 , '1,3,11' TXT FROM DUAL 
) , T_MAP ( CD , NM ) AS ( 
SELECT '1'  , '사과' FROM DUAL UNION ALL
SELECT '2'  , '배' FROM DUAL UNION ALL
SELECT '3'  , '감' FROM DUAL UNION ALL
SELECT '4'  , '귤' FROM DUAL UNION ALL
SELECT '11' , '오렌지' FROM DUAL UNION ALL
SELECT '12' , '바나나' FROM DUAL UNION ALL
SELECT '13' , '파인애플' FROM DUAL UNION ALL
SELECT '14' , '자몽' FROM DUAL 
)

SELECT LISTAGG(B.NM,',') WITHIN GROUP (ORDER BY RN ) 
  FROM (SELECT LV RN , REGEXP_SUBSTR(TXT,'[^,]+',1,LV) CD
          FROM T AA
             , (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 15  ) BB
         WHERE REGEXP_COUNT(TXT,',') + 1 >= LV
        ) A
      , T_MAP B
  WHERE A.CD = B.CD

 


by 마농 [2019.02.08 14:29:02]
WITH t AS
(
SELECT 1 seq, '1,3,11' txt FROM dual
UNION ALL SELECT 2, '3,1,11' FROM dual
)
, t_map AS
(
SELECT '1' cd, '사과' nm FROM dual
UNION ALL SELECT  '2', '배'       FROM dual
UNION ALL SELECT  '3', '감'       FROM dual
UNION ALL SELECT  '4', '귤'       FROM dual
UNION ALL SELECT '11', '오렌지'   FROM dual
UNION ALL SELECT '12', '바나나'   FROM dual
UNION ALL SELECT '13', '파인애플' FROM dual
UNION ALL SELECT '14', '자몽'     FROM dual
)
SELECT a.seq, a.txt
     , LISTAGG(b.nm, ',') WITHIN GROUP(ORDER BY INSTR(','||a.txt||',', ','||b.cd||',')) nm
  FROM t a
     , t_map b
 WHERE INSTR(','||a.txt||',', ','||b.cd||',') > 0
 GROUP BY a.seq, a.txt
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입