첨부 소스 코드는 나눔고딕코딩 폰트를 사용합니다.
728x90
반응형
728x170
WITH SOURCE_TABLE AS
(
    SELECT 1 ID, 'A|B|C' SOURCE_STRING FROM DUAL UNION ALL
    SELECT 2 ID, 'B|C'   SOURCE_STRING FROM DUAL UNION ALL
    SELECT 3 ID, 'A|C'   SOURCE_STRING FROM DUAL UNION ALL
    SELECT 4 ID, 'A|B|C' SOURCE_STRING FROM DUAL UNION ALL
    SELECT 5 ID, 'B|C'   SOURCE_STRING FROM DUAL UNION ALL
    SELECT 6 ID, 'B|C|D' SOURCE_STRING FROM DUAL
)
SELECT
    A.ID
   ,B.SOURCE_ELEMENT
FROM SOURCE_TABLE A,
(
    SELECT DISTINCT REGEXP_SUBSTR(C.SOURCE_ELEMENT, '[^|]+', 1, LEVEL) SOURCE_ELEMENT
    FROM
    (
        SELECT REPLACE(WM_CONCAT(DISTINCT D.SOURCE_STRING), ',', '|') SOURCE_ELEMENT
        FROM SOURCE_TABLE D
    ) C
    CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(C.SOURCE_ELEMENT, '[^|]+', '')) + 1
) B
WHERE INSTR('|' || A.SOURCE_STRING || '|',  '|' || B.SOURCE_ELEMENT || '|') > 0
ORDER BY A.ID, B.SOURCE_ELEMENT;
728x90
반응형
그리드형(광고전용)
Posted by icodebroker

댓글을 달아 주세요