with MODEL_DESC as (
select MODEL_TYPE, MODEL_DEV_NM
from TB_SBRCMxxx
UNION ALL
select MODEL_TYPE, MODEL_DEV_NM
from TB_SWQTMxxx
)
select substr(max(SYS_CONNECT_BY_PATH (REGEXP_REPLACE(SUBSTR(SUBSTR(MODEL_DEV_NM, 1, LENGTH(MODEL_DEV_NM)-4),5),'[0-9]','#') || SUBSTR(MODEL_DEV_NM,-4,4) , ',')), 2) as MODEL_DEV_NM
FROM (
SELECT MODEL_DEV_NM, ROWNUM as RNUM
from MODEL_DESC
) START WITH RNUM = 1 CONNECT BY PRIOR RNUM = RNUM - 1
with MODEL_DESC as (
select MODEL_TYPE, MODEL_DEV_NM
from TB_SBRCMxxx
UNION ALL
select MODEL_TYPE, MODEL_DEV_NM
from TB_SWQTMxxx
)
select MODEL_TYPE, substr(max(SYS_CONNECT_BY_PATH (REGEXP_REPLACE(SUBSTR(SUBSTR(MODEL_DEV_NM, 1, LENGTH(MODEL_DEV_NM)-4),5),'[0-9]','#') || SUBSTR(MODEL_DEV_NM,-4,4) , ',')), 2) as MODEL_DEV_NM
from (
select MODEL_TYPE, MODEL_DEV_NM, ROW_NUMBER() OVER (PARTITION by MODEL_TYPE order by MODEL_DEV_NM) RNUM
from(
select MODEL_DEV_NM, MODEL_TYPE, ROWNUM as RNUM
from MODEL_DESC
)
)
START WITH RNUM = 1 CONNECT BY PRIOR RNUM = RNUM - 1
and PRIOR MODEL_TYPE = MODEL_TYPE
group by MODEL_TYPE
order by MODEL_TYPE
- 조회된 SQL 쿼리의 열을 구분자를 이용하여 행으로 나열하고 싶을때 사용
- 연결하려는 문자열의 수가 많아질 경우 쿼리의 performance 문제 발생 여지가 있음.
- Oracle 10g 부터 사용
'DB' 카테고리의 다른 글
[Oracle] UNION & UNION ALL (0) | 2014.10.29 |
---|---|
[Oracle] CONNECT BY LEVEL (0) | 2014.10.27 |
[Oracle] with 절 (0) | 2014.10.27 |
[Oracle]REGEXP_REPLACE (0) | 2014.09.29 |
[Oracle]피벗(Pivot)과 언피벗(UnPivot) (0) | 2014.09.25 |