'필드열나열'에 해당되는 글 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  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 부터 사용