질문의 내용을 확실히 이해는 못하겠으나 아래와 같은 결과를 얻으시려는건가요?
WITH A AS(
SELECT 'A' NAME, '10' VAL1, '20' VAL2 FROM DUAL UNION ALL
SELECT 'B' NAME, '10' VAL1, '30' VAL2 FROM DUAL UNION ALL
SELECT 'C' NAME, '20' VAL1, '20' VAL2 FROM DUAL UNION ALL
SELECT 'D' NAME, '20' VAL1, '30' VAL2 FROM DUAL UNION ALL
SELECT 'E' NAME, '30' VAL1, '10' VAL2 FROM DUAL
),B AS (
SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL<=2
)
SELECT
DECODE(LV,1,NAME,2,NAME2) NAME
,DECODE(LV,1,VAL1,2,VAL3) VAL1
,DECODE(LV,1,VAL2,2,VAL4) VAL2
FROM (
SELECT
NAME,VAL1,VAL2,LV
,DECODE(VAL1,'20',NAME) NAME2
,DECODE(VAL1,'20','10') VAL3
,DECODE(VAL1,'20',VAL2) VAL4
FROM A,B
WHERE 1=1
)
WHERE 1=1
AND DECODE(LV,1,NAME,2,NAME2) IS NOT NULL
ORDER BY NAME,VAL1 DESC
;
-- 그냥 코드는 없다고 생각하고 풀어 봅니다. -- C 는 20 만 있어서 복제 대상 -- D 는 10 이 이미 있어서 복제 안함 WITH t AS ( SELECT 'A' nm, 10 v1, 20 v2 FROM dual UNION ALL SELECT 'B', 10, 30 FROM dual UNION ALL SELECT 'C', 20, 20 FROM dual UNION ALL SELECT 'D', 20, 40 FROM dual UNION ALL SELECT 'D', 10, 50 FROM dual ) SELECT nm, v1, v2 FROM (SELECT nm , DECODE(lv, 2, 10, v1) v1 , v2 , ROW_NUMBER() OVER(PARTITION BY nm, DECODE(lv, 2, 10, v1) ORDER BY lv) rn FROM t , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 2) WHERE lv <= DECODE(v1, 20, 2, 1) ) WHERE rn = 1 ; WITH t AS ( SELECT 'A' nm, 10 v1, 20 v2 FROM dual UNION ALL SELECT 'B', 10, 30 FROM dual UNION ALL SELECT 'C', 20, 20 FROM dual UNION ALL SELECT 'D', 20, 40 FROM dual UNION ALL SELECT 'D', 10, 50 FROM dual ) SELECT nm, v1, v2 FROM t UNION ALL SELECT a.nm , 10 v1 , a.v2 FROM t a , t b WHERE a.v1 = 20 AND b.v1(+) = 10 AND b.nm(+) = a.nm AND b.nm IS NULL ORDER BY 1, 2 ;