8-40 pivotとOuter unionもどき

SQLパズル

BaseTable       AdvTable
PKey  Val       PKey  Val
----  ----      ----  ----
   1  AAAA         1  DDDD
   2  BBBB         3  EEEE
   3  CCCC         4  FFFF

baseテーブルは、そのまま出力する。
(対応するbaseテーブルの行がある)abvテーブルの行は、baseテーブルの情報を付与して出力する。

出力結果
PKey  base  adv
----  ----  ----
   1  AAAA  null
   2  BBBB  null
   3  CCCC  null
   1  AAAA  DDDD
   3  CCCC  EEEE

データ作成スクリプト

create table BaseTable(PKey primary key,Val) as
select 1,'AAAA' from dual union
select 2,'BBBB' from dual union
select 3,'CCCC' from dual;

create table AdvTable(PKey primary key,Val) as
select 1,'DDDD' from dual union
select 3,'EEEE' from dual union
select 4,'FFFF' from dual;

SQL

--■■■union allを使う方法■■■
select PKey,Val as base,to_char(null) as adv from BaseTable
union all
select b.PKey,a.Val,b.Val
  from BaseTable a,AdvTable b
 where a.PKey=b.PKey
order by PKey,base,adv;

--■■■テーブルにIDを付与する方法■■■
select PKey,base,adv
from (select ID,PKey,
      max(decode(ID,1,Val)) over(partition by PKey) as base,
      decode(ID,2,Val) as adv,
      max(case when ID=1 then 1 else 0 end) over(partition by PKey) as willOut
      from (select 1 as ID,PKey,Val from BaseTable union all
            select 2      ,PKey,Val from AdvTable))
 where willOut=1
order by ID,PKey;

--■■■Outer unionもどきを使う方法■■■
select PKey,base,adv
from (select PKey,max(base) over(partition by PKey) as base,adv,
      count(base) over(partition by PKey) as willOut
      from (select PKey,Val as base,to_char(null) as adv from BaseTable union all
            select PKey,to_char(null),Val from AdvTable))
 where willOut=1
order by adv nulls first,PKey;

解説

完全外部結合と、Outer unionもどきは
欲しい結果によって使い分けるといいでしょう。

---