데이터베이스 질문과 답변
with temp as (
select 1 as num1, 'kim' as boss_nm1, 'F1' as type1, 2 as num2, null as boss_nm2, null as type2 union all
select 1 as num1, 'kim' as boss_nm1, 'Z2' as type1, 2 as num2, null as boss_nm2, null as type2 union all
select 1 as num1, null as boss_nm1, null as type1, 2 as num2, 'pak' as boss_nm2, 'F1' as type2 union all
select 1 as num1, null as boss_nm1, null as type1, 2 as num2, 'pak' as boss_nm2, 'C1' as type2 union all
select 1 as num1, null as boss_nm1, null as type1, 2 as num2, 'pak' as boss_nm2, 'D1' as type2
)
select t.* from temp t
이렇게 해서
---------------------------------
1 kim F1 2 NULL NULL
--------------------------------
1 kim Z2 2 NULL NULL
---------------------------------
1 NULL NULL 2 pak F1
-------------------------------
1 NULL NULL 2 pak C1
--------------------------------
1 NULL NULL 2 pak D1
가 아니고
---------------------------------
1 kim F1 2 pak F1
--------------------------------
1 kim Z2 2 pak C1
--------------------------------
1 NULL NULL 2 pak D1
표현하고 싶습니다.
-- 1. 집합을 두개로 분리하고
-- 2. 순번을 부여한 뒤
-- 3. 키 + 순번으로 풀아우터조인
SELECT NVL(a.num1, b.num1) num1, a.boss_nm1, a.type1
, NVL(a.num2, b.num2) num2, b.boss_nm2, b.type2
FROM (SELECT num1, num2
, boss_nm1, type1
, ROW_NUMBER() OVER(PARTITION BY num1, num2 ORDER BY type1) rn
FROM temp
WHERE boss_nm1 IS NOT NULL
) a
FULL OUTER JOIN
(SELECT num1, num2
, boss_nm2, type2
, ROW_NUMBER() OVER(PARTITION BY num1, num2 ORDER BY type2) rn
FROM temp
WHERE boss_nm2 IS NOT NULL
) b
ON a.num1 = b.num1
AND a.num2 = b.num2
AND a.rn = b.rn
ORDER BY num1, num2, NVL(a.rn, b.rn)
;
아 정말 감사드립니다.
그런 귀중한 쿼리를 가지고 분석을 많이 해보겠습니다
다시한번 감사드려요 ㅠ_ㅠ
) 버튼을 클릭하여 작성 하시면 됩니다.