WITH TEMP AS
(
SELECT 1 ID,'employee' EXT_KEY,'comment1' EXT_STRING_VAL FROM DUAL
UNION
SELECT 2 ID,'manager' EXT_KEY,'comment2' EXT_STRING_VAL FROM DUAL
UNION
SELECT 3 ID,'employee' EXT_KEY,'comment3' EXT_STRING_VAL FROM DUAL
UNION
SELECT 4 ID,'manager' EXT_KEY,'comment4' EXT_STRING_VAL FROM DUAL
)
SELECT REGEXP_SUBSTR(EMPLOYEE,'[[:alnum:]]+',1,RN) EMPLOYEE,
REGEXP_SUBSTR(MANAGER,'[[:alnum:]]+',1,RN) MANAGER
FROM
(
SELECT NVL2(EMPLOYEE,EMPLOYEE,MAX(CASE WHEN EMPLOYEE IS NOT NULL THEN EMPLOYEE END) OVER(ORDER BY EMPLOYEE)) EMPLOYEE,
NVL2(MANAGER,MANAGER,MAX(CASE WHEN MANAGER IS NOT NULL THEN MANAGER END) OVER(ORDER BY MANAGER)) MANAGER,
ROWNUM RN
FROM
(SELECT DECODE(EXT_KEY,'employee',EXT_STRING_VAL) employee,
DECODE(EXT_KEY,'manager',EXT_STRING_VAL) manager
FROM (SELECT EXT_KEY,WMSYS.WM_CONCAT(EXT_STRING_VAL) EXT_STRING_VAL FROM TEMP GROUP BY EXT_KEY)
) ORDER BY ROWNUM
)
EMPLOYEE
MANAGER
comment1
comment2
comment3
comment4
|
