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

컬럼 20개를 같은 코드테이블 조인시/function/데이터표현

by 손님 [SQL Query] 조인 function 데이터 표현 [2013.03.29 15:52:52]

안녕하세요 항상 보기만하다가 처음으로 용기를 내서 qna게시판을 이용해 보네요
도저히 개념이 서질 않아 이렇게 글을 올립니다.

TABLE
(테이블명) servicecode mainTable
servicecode, servicename mainseq, servicecode1,servicecode2,.......servicecode20, A컬럼, B컬럼
-------------------------------------------- ------------------------------------------------------------------------------------------------------- --
A     ,서비스1 1 , A C
B ,서비스2   2 , C F
C ,서비스3   3 , D
D ,서비스4   4 , H , I ... F
E ,서비스5   5 , D F .... C
F ,서비스6   6 ,
G ,서비스7   7 ,
...... .......
,서비스20 10000 A

이렇게 2개 테이블이 있습니다.
mainTable에서 서비스 테이블 코드값을 가지고 있는 상태인데
값이 순차적으로 들어가지 않고 산발적으로 들어가 있는 상태이고
servicecode에 join하여 아래와같이 servicename값을 가지고 와야 합니다.
기본적인 outer join을 하게되면 조인을 20번을 걸어 주어야 되서 조인을 하지말고 function으로 처리하려고 하는데
도무지 방법을 모르겠어요 ㅠㅠ
function으로 서비스명은 바꾼다 치지만 저 값사이 슬러시는 대체 어떻게 처리를 해야 되는건지...
또 조회시 servicecode A가 속하는데이터 조회할때 servicecode1='A' or servicecode2='A' ....servicecode20='A'
이런 식으로 밖에 조회 할 수 없는건지 고수님들 의견주세요

아래는 원하는 데이터 형태입니다.

mainTable
mainseq, totalserviceName A컬럼, B컬럼
--------------------------------------------------------------------------------------
1 , 서비스1/서비스3
2 , 서비스3/서비스6
3 , 서비스4
4 , 서비스8/서비스9/..../서비스6
5 , 서비스4/서비스6/..../서비스3
..........
10000, 서비스1

by 아린 [2013.03.29 16:12:46]

참고하세요..

WITH servicecode(scode, sname) AS(

SELECT 'A', '서비스1' FROM dual UNION ALL

SELECT 'B', '서비스2' FROM dual UNION ALL

SELECT 'C', '서비스3' FROM dual UNION ALL

SELECT 'D', '서비스4' FROM dual UNION ALL

SELECT 'E', '서비스5' FROM dual

)

, maintable(mainseq, scode1, scode2, scode3, scode4, acol, bcol) AS(

SELECT 1, 'A', 'B', 'C', 'D', 'aa1', 'bb2' FROM dual UNION ALL

SELECT 2, 'C', '', 'A', '', 'aa2', 'bb2' FROM dual UNION ALL

SELECT 3, '', '', 'E', 'A', 'aa3', 'bb3' FROM dual

)

SELECT mainseq

, LISTAGG(sname,'/') WITHIN GROUP(ORDER BY m.scode) sname

, MIN(acol) acol, MIN(bcol) bcol

FROM (SELECT mainseq

, DECODE(lv, 1, scode1, 2, scode2, 3, scode3, 4, scode4) scode

, acol, bcol

FROM maintable

, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 20)

) m

, servicecode s

WHERE m.scode = s.scode

AND m.scode IS NOT NULL

GROUP BY mainseq

ORDER BY mainseq

by 마농 [2013.03.29 16:17:22]

-- 함수를 하나 만들어 놓으면 사용(개발)하긴 편하지만 성능이 떨어질 수 있습니다.
-- 코드의 종류가 많지 않다면 스칼라서브쿼리가 성능에 좋습니다.
-- 같은 코드로 조회시 이전 조회시 캐시된 결과를 재사용합니다.
-- 1. 스칼라서브쿼리 : 무식해 보일수도잇지만, 성능 비교 해봐야 함 --
SELECT mainseq
     , SUBSTR((SELECT '/'||servicename FROM servicecode WHERE servicecode = m.servicecode1)
           || (SELECT '/'||servicename FROM servicecode WHERE servicecode = m.servicecode2)
           || (SELECT '/'||servicename FROM servicecode WHERE servicecode = m.servicecode3)
           || (SELECT '/'||servicename FROM servicecode WHERE servicecode = m.servicecode4)
           || (SELECT '/'||servicename FROM servicecode WHERE servicecode = m.servicecode5)
           || (SELECT '/'||servicename FROM servicecode WHERE servicecode = m.servicecode6)
           || (SELECT '/'||servicename FROM servicecode WHERE servicecode = m.servicecode7)
           || (SELECT '/'||servicename FROM servicecode WHERE servicecode = m.servicecode8)
           || (SELECT '/'||servicename FROM servicecode WHERE servicecode = m.servicecode9)
           || (SELECT '/'||servicename FROM servicecode WHERE servicecode = m.servicecode10)
           || (SELECT '/'||servicename FROM servicecode WHERE servicecode = m.servicecode11)
           || (SELECT '/'||servicename FROM servicecode WHERE servicecode = m.servicecode12)
           || (SELECT '/'||servicename FROM servicecode WHERE servicecode = m.servicecode13)
           || (SELECT '/'||servicename FROM servicecode WHERE servicecode = m.servicecode14)
           || (SELECT '/'||servicename FROM servicecode WHERE servicecode = m.servicecode15)
           || (SELECT '/'||servicename FROM servicecode WHERE servicecode = m.servicecode16)
           || (SELECT '/'||servicename FROM servicecode WHERE servicecode = m.servicecode17)
           || (SELECT '/'||servicename FROM servicecode WHERE servicecode = m.servicecode18)
           || (SELECT '/'||servicename FROM servicecode WHERE servicecode = m.servicecode19)
           || (SELECT '/'||servicename FROM servicecode WHERE servicecode = m.servicecode20)
           , 2) AS totalserviceName
     , A컬럼, B컬럼
  FROM mainTable m
;
-- 2. 조인, Group By
SELECT m.mainseq
     , REPLACE(wm_concat(s.servicename), ',', '/') totalserviceName
     , m.A컬럼, m.B컬럼
  FROM mainTable m
     , servicecode s
 WHERE s.servicecode
    IN ( m.servicecode1 , m.servicecode2 , m.servicecode3 , m.servicecode4 , m.servicecode5 
       , m.servicecode6 , m.servicecode7 , m.servicecode8 , m.servicecode9 , m.servicecode10
       , m.servicecode11, m.servicecode12, m.servicecode13, m.servicecode14, m.servicecode15
       , m.servicecode16, m.servicecode17, m.servicecode18, m.servicecode19, m.servicecode20
       )
 GROUP BY m.mainseq, m.A컬럼, m.B컬럼
;
-- 3. 짬뽕 방식. 스칼라서브쿼리 캐시효과 없을 듯.
SELECT m.mainseq
     , (SELECT REPLACE(wm_concat(s.servicename), ',', '/')
          FROM servicecode s
         WHERE s.servicecode
            IN ( m.servicecode1 , m.servicecode2 , m.servicecode3 , m.servicecode4 , m.servicecode5 
               , m.servicecode6 , m.servicecode7 , m.servicecode8 , m.servicecode9 , m.servicecode10
               , m.servicecode11, m.servicecode12, m.servicecode13, m.servicecode14, m.servicecode15
               , m.servicecode16, m.servicecode17, m.servicecode18, m.servicecode19, m.servicecode20
               )
        ) totalserviceName
     , A컬럼, B컬럼
  FROM mainTable m
;

by 손님 [2013.03.29 16:40:35]

우와 완전 멋지십니다.
머리의 한계를 느끼고 있던 순간에 이런 멋진 결과를 주시다니요!!
정말 많이 공부를 해야 된다는 아픔을 느낍니다.
정말 감사드립니다. ^_____^

by 손님 [2013.03.29 18:14:32]

오라클 11g가 아니여서 함수가 안먹혀요 ㅠ_ㅠ

by 마농 [2013.03.29 18:42:21]

http://www.gurubee.net/article/55512

댓글등록

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

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

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

데이터베이스 질문과 답변