데이터베이스 질문과 답변
| A | 1 |
| B | 2 |
| c | 3 |
1번테이블
| A | 11 |
| A | |
| A | 33 |
| B | 11 |
| B | 22 |
| D | 44 |
2번테이블
WITH TONE AS (SELECT 'A' COL1 , '1' COL2 FROM DUAL UNION ALL
SELECT 'B' COL1, '2' COL2 FROM DUAL UNION ALL
SELECT 'C' COL1, '3' COL2 FROM DUAL),
TTWO AS (SELECT 'A' COL1 , '11' COL2 FROM DUAL UNION ALL
SELECT 'A' COL1 , '' COL2 FROM DUAL UNION ALL
SELECT 'A' COL1 , '33' COL2 FROM DUAL UNION ALL
SELECT 'B' COL1, '11' COL2 FROM DUAL UNION ALL
SELECT 'B' COL1, '22' COL2 FROM DUAL UNION ALL
SELECT 'D' COL1, '44' COL2 FROM DUAL )
이 두테이블을 조인해서 결과값이
A 1
A 11
A 1
A
A 1
A 33
B 2
B 11
B 2
B 22
C 3
C
결과는 이렇게 나와야 합니다. 풀릴듯 말듯 안풀려서 질문해 봄니다 ㅠㅠ
감사합니다~
by jkson
[2017.02.28 13:38:29]
with t1 as
(
select 'A' code, '1' val from dual union all
select 'B' code, '2' val from dual union all
select 'C' code, '3' val from dual
)
,t2 as
(
select 'A' code, '11' val from dual union all
select 'A' code, '' val from dual union all
select 'A' code, '33' val from dual union all
select 'B' code, '11' val from dual union all
select 'B' code, '22' val from dual union all
select 'D' code, '44' val from dual
)
select a.code, decode(lv,1,a.val, b.val) val
from t1 a
, t2 b
,(select level lv from dual connect by level <= 2)
where a.code = b.code(+)
by DISTINCT
[2017.02.28 13:39:59]
우앙 빠른 답변 감사합니다. ~~
by 제로
[2017.03.02 15:36:01]
-- 11g이상
with t1 as
(
select 'A' code, '1' val from dual union all
select 'B' code, '2' val from dual union all
select 'C' code, '3' val from dual
)
,t2 as
(
select 'A' code, '11' val from dual union all
select 'A' code, '' val from dual union all
select 'A' code, '33' val from dual union all
select 'B' code, '11' val from dual union all
select 'B' code, '22' val from dual union all
select 'D' code, '44' val from dual
)
select code, val
from (
select a.code, a.val v1, b.val v2
from t1 a
, t2 b
where a.code = b.code(+)
)
unpivot include nulls( val for col in (v1, v2)) ;
) 버튼을 클릭하여 작성 하시면 됩니다.