데이터베이스 질문과 답변
by 디케이 [2013.05.09 12:08:06]
다수의 컬럼에 콤마를 구분자로 코드값들이 들어 있습니다.
각각의 값들을 분리해서 새로운 ROW 데이터를 만들려고 합니다.
예를들면
WITH T(ALCD, CAPA, INJI, RESN) AS
( SELECT 'RR01', '3300,3800', 'GDI', 'KOR' FROM DUAL
UNION ALL SELECT 'RR02', '2000,2400', 'MPI,GDI', 'KOR,EUR' FROM DUAL
UNION ALL SELECT 'RR03', '1000', '', 'KOR' FROM DUAL
)
예상결과 ==>
ALCD CAPA INJI RESN
---- ---- ---- ----
RR01 3300 GDI KOR
RR01 3800 GDI KOR
RR02 2000 MIP KOR
RR02 2000 MIP EUR
RR02 2000 GDI KOR
RR02 2000 GDI EUR
RR02 2400 MIP KOR
RR02 2400 MIP EUR
RR02 2400 GDI KOR
RR02 2400 GDI EUR
RR03 1000 KOR
--------------------
실제 데이터가 1000건 정도 됩니다.
이걸 위와같이 나열하면 몇건 나올지 ㅎㄷㄷ..
고수분들의 도움 바랍니다.
항상 감사합니다.
WITH T(ALCD, CAPA, INJI, RESN) AS
(
SELECT 'RR01', '3300,3800', 'GDI' , 'KOR' FROM DUAL UNION ALL
SELECT 'RR02', '2000,2400', 'MPI,GDI', 'KOR,EUR' FROM DUAL UNION ALL
SELECT 'RR03', '1000' , '' , 'KOR' FROM DUAL
)
, C AS
( SELECT LEVEL LV
FROM DUAL
CONNECT BY LEVEL <= 10
)
select ALCD
, REGEXP_SUBSTR ( CAPA, '[^,]+', 1, b.lv )
, REGEXP_SUBSTR ( INJI, '[^,]+', 1, c.lv )
, REGEXP_SUBSTR ( RESN, '[^,]+', 1, d.lv )
from T a
, C b
, C c
, C d
where b.lv <= NVL ( length ( a.CAPA ) - length ( replace ( a.CAPA, ',', '' ) ) + 1, 1 )
and c.lv <= NVL ( length ( a.INJI ) - length ( replace ( a.INJI, ',', '' ) ) + 1, 1 )
and d.lv <= NVL ( length ( a.RESN ) - length ( replace ( a.RESN, ',', '' ) ) + 1, 1 )
답변 감사합니다.
CONNECT BY LEVEL <= 10
10으로 고정하셨는데 이부분은 뭐 Max 값 구하면 될 것 같네요
실제 쿼리를 구현해 봐야 겠습니다. 응답속도가 잘 나와야 될텐데...
아.. 한방에 조인으로 처리하셨군요 완전 깔끔..
채용근님 감사합니다.
응답속도가 1초안에 떨어지네요..^^
뭐 다른 거 모두 빼고 다중입력된 컬럼만 대상으로해서 조회한거라 실제 응답속도하고는 차이는 있겠지만
생각했던거 이상으로 빠른응답속도입니다...
-- 답만 맞추는 아찌 ㅜㅜ 정렬은 못했어요
WITH T(ALCD, CAPA, INJI, RESN) AS
( SELECT 'RR01', '3300,3800', 'GDI', 'KOR' FROM DUAL
UNION ALL SELECT 'RR02', '2000,2400', 'MPI,GDI', 'KOR,EUR' FROM DUAL
UNION ALL SELECT 'RR03', '1000', '', 'KOR' FROM DUAL
)
SELECT ALCD
,DECODE(CAPA,'@','',CAPA) CAPA
,DECODE(INJI,'@','',INJI) INJI
,DECODE(RESN,'@','',RESN) RESN
FROM
(SELECT T.ALCD , A.CAPA , B.INJI , C.RESN FROM T ,
(SELECT ALCD ,REGEXP_SUBSTR(NVL(CAPA,'@'), '[^,]+', 1, lv ) CAPA
FROM T , (SELECT LEVEL lv FROM DUAL CONNECT BY LEVEL <= 100 )) A ,
(SELECT ALCD ,REGEXP_SUBSTR(NVL(INJI,'@'), '[^,]+', 1, lv ) INJI
FROM T , (SELECT LEVEL lv FROM DUAL CONNECT BY LEVEL <= 100 )) B ,
(SELECT ALCD ,REGEXP_SUBSTR(NVL(RESN,'@'), '[^,]+', 1, lv ) RESN
FROM T , (SELECT LEVEL lv FROM DUAL CONNECT BY LEVEL <= 100 )) C
WHERE T.ALCD = A.ALCD(+)
AND T.ALCD = B.ALCD(+)
AND T.ALCD = C.ALCD(+)
)
WHERE CAPA IS NOT NULL
AND INJI IS NOT NULL
AND RESN IS NOT NULL
WITH T(ALCD, CAPA, INJI, RESN) AS
( SELECT 'RR01', '3300,3800', 'GDI', 'KOR' FROM DUAL
UNION ALL SELECT 'RR02', '2000,2400', 'MPI,GDI', 'KOR,EUR' FROM DUAL
UNION ALL SELECT 'RR03', '1000', '', 'KOR' FROM DUAL
)
select alcd , capa, inji , resn
from
(
select alcd
, regexp_substr( capa, '[^,]+' , 1, 1) as capa1
, regexp_substr( capa, '[^,]+' , 1, 2) as capa2
, nvl(regexp_substr( inji, '[^,]+' , 1, 1),' ') as inji1
, regexp_substr( inji, '[^,]+' , 1, 2) as inji2
, regexp_substr( resn, '[^,]+' , 1, 1) as resn1
, regexp_substr( resn, '[^,]+' , 1, 2) as resn2
from t
)
unpivot ( capa for a in ( capa1 , capa2 ) )
unpivot ( inji for b in ( inji1 , inji2 ) )
unpivot ( resn for c in ( resn1 , resn2 ) )