-- Oracle -- WITH t AS ( SELECT 'A' cd, 100 v, 'Y' yn FROM dual UNION ALL SELECT 'B', 200, 'Y' FROM dual UNION ALL SELECT 'C', 300, 'Y' FROM dual UNION ALL SELECT 'D', 400, 'Y' FROM dual UNION ALL SELECT 'Z', 1000, 'N' FROM dual UNION ALL SELECT 'X', 1100, 'N' FROM dual UNION ALL SELECT 'V', 1200, 'N' FROM dual ) -- 1. 전통 방식 MIN(DECODE( 10G 이전 SELECT rn , MIN(DECODE(yn, 'Y', cd)) y_cd , MIN(DECODE(yn, 'Y', v )) y_v , MIN(DECODE(yn, 'N', cd)) n_cd , MIN(DECODE(yn, 'N', v )) n_v FROM (SELECT cd, v, yn , ROW_NUMBER() OVER(PARTITION BY yn ORDER BY v, cd) rn FROM t ) GROUP BY rn ORDER BY rn ; -- 2. PIVOT 11G 이후 SELECT * FROM (SELECT cd, v, yn , ROW_NUMBER() OVER(PARTITION BY yn ORDER BY v, cd) rn FROM t ) PIVOT (MIN(cd) cd, MIN(v) v FOR yn IN ('Y' y, 'N' n)) ORDER BY rn ;
1. full outer join
select * from
(select row_number() over( order by 컬럼2 ) rn , 컬럼1 , 컬럼2 where 컬럼3 = ‘Y’ ) a
full outer join
(select row_number() over( order by 컬럼2 ) rn ,
컬럼1 , 컬럼2 where 컬럼3 = ‘N’ ) b
on a.rn = b.rn
2. row_number() ovet( partition by 컬럼3 order by 컬럼2 이용하셔서 group by rn 하셔서 max값으로 추출하시면 됩니다