-- 1번
WITH A AS (
SELECT '' V5, '' V4, 'AAA' V1, '' V0 FROM DUAL
UNION ALL
SELECT '' V5, '' V4, 'BBB' V1, '' V0 FROM DUAL
UNION ALL
SELECT '' V5, '' V4, '' V1, 'CCC' V0 FROM DUAL
UNION ALL
SELECT 'DDD' V5, '' V4, '' V1, '' V0 FROM DUAL
UNION ALL
SELECT '' V5, 'EEE' V4, '' V1, '' V0 FROM DUAL
)
SELECT * FROM A;
-- 2번
WITH A AS (
SELECT 'DDD' V5, 'EEE' V4, 'AAA' V1, 'CCC' V0 FROM DUAL
UNION ALL
SELECT '' V5, '' V4, 'BBB' V1, '' V0 FROM DUAL
)
SELECT * FROM A
위 1번과 같은 결과 데이터를 가지고 2번과 같은 결과를 얻고 싶은데... 고수님들의 도움 부탁드립니다.
WITH A AS ( SELECT '' V5, '' V4, 'AAA' V1, '' V0 FROM DUAL UNION ALL SELECT '' V5, '' V4, 'BBB' V1, '' V0 FROM DUAL UNION ALL SELECT '' V5, '' V4, '' V1, 'CCC' V0 FROM DUAL UNION ALL SELECT 'DDD' V5, '' V4, '' V1, '' V0 FROM DUAL UNION ALL SELECT '' V5, 'EEE' V4, '' V1, '' V0 FROM DUAL ) SELECT RN , MIN(V5) V5 , MIN(V4) V4 , MIN(V1) V1 , MIN(V0) V0 FROM ( SELECT A.* , LEAST ( ROW_NUMBER() OVER(ORDER BY V5 ) , ROW_NUMBER() OVER(ORDER BY V4 ) , ROW_NUMBER() OVER(ORDER BY V1 ) , ROW_NUMBER() OVER(ORDER BY V0 ) ) RN FROM A ) GROUP BY RN ;