[Oracle Database] 컬럼 병합
방법 1. FULL JOIN
WITH TEMP1 AS (
SELECT 1 AS NO, 'AAA' AS NAME FROM DUAL UNION
SELECT 2 AS NO, 'BBB' AS NAME FROM DUAL UNION
SELECT 3 AS NO, 'CCC' AS NAME FROM DUAL
), TEMP2 AS (
SELECT 3 AS NO, 'C@C.COM' AS MAIL FROM DUAL UNION
SELECT 4 AS NO, 'D@D.COM' AS MAIL FROM DUAL UNION
SELECT 5 AS NO, 'E@E.COM' AS MAIL FROM DUAL
)
SELECT NVL(TEMP1.NO, TEMP2.NO) AS NO, NAME, MAIL
FROM TEMP1 FULL JOIN TEMP2 ON TEMP1.NO = TEMP2.NO
ORDER BY TEMP1.NO
방법 2. UNION
WITH TEMP1 AS (
SELECT 1 AS NO, 'AAA' AS NAME FROM DUAL UNION
SELECT 2 AS NO, 'BBB' AS NAME FROM DUAL UNION
SELECT 3 AS NO, 'CCC' AS NAME FROM DUAL
), TEMP2 AS (
SELECT 3 AS NO, 'C@C.COM' AS MAIL FROM DUAL UNION
SELECT 4 AS NO, 'D@D.COM' AS MAIL FROM DUAL UNION
SELECT 5 AS NO, 'E@E.COM' AS MAIL FROM DUAL
)
SELECT NO, MAX(NAME), MAX(MAIL)
FROM (
SELECT NO, NAME, '' AS MAIL
FROM TEMP1
UNION
SELECT NO, '' AS NAME, MAIL
FROM TEMP2
)
GROUP BY NO
ORDER BY NO
결과 예시
대상 1:
대상 2:
결과:
