model句14 外部結合してPivot

SQLパズル

IDTable           KeyTable
ID  Key1  Key2    Key  Name
--  ----  ----    ---  ----
10     1     2      1  AAA
20     3     4      2  BBB
30     5     6      3  CCC
40     7     8      6  DDD

IDごとに、
Key1に紐づく、KeyTableのNameをName1として求め、
Key2に紐づく、KeyTableのNameもName2として求める。

出力結果
ID  Key1  Name1  Key2  Name2
--  ----  -----  ----  ----
10     1  AAA       2  BBB
20     3  CCC       4  null
30     5  null      6  DDD
40     7  null      8  null

データ作成スクリプト

create table IDTable(ID,Key1,Key2) as
select 10,1,2 from dual union all
select 20,3,4 from dual union all
select 30,5,6 from dual union all
select 40,7,8 from dual;

create table KeyTable(Key,Name) as
select 1,'AAA' from dual union all
select 2,'BBB' from dual union all
select 3,'CCC' from dual union all
select 6,'DDD' from dual;

SQL

col Name1 for a10
col Name2 for a10

--■■■model句を使う方法(10g以降)■■■
select ID,Key1,Name1,Key2,Name2
from (select a.ID,a.Key1,a.Key2,b.Key,b.Name,
      Row_Number() over(partition by a.ID order by b.Key) as Rn
        from IDTable a Left Join KeyTable b
          on b.Key in(a.Key1,Key2))
 model RETURN UPDATED ROWS
 dimension by (ID,Rn)
 measures(Key,Key1,Name as Name1,Key2,Name as Name2,Name as willSet)
 rules(Name1[ID,1] = max(decode(Key,Key1,willSet))[CV(),any],
       Name2[ID,1] = max(decode(Key,Key2,willSet))[CV(),any])
order by ID;

--■■■よくあるPivot■■■
select ID,
max(Key1) as Key1,max(decode(key,Key1,Name)) as name1,
max(Key2) as key2,max(decode(key,Key2,Name)) as Name2
from (select a.ID,a.Key1,a.Key2,b.Key,b.Name
        from IDTable a Left Join KeyTable b
          on b.Key in(a.Key1,Key2))
group by ID
order by ID;

解説

この場合は、よくあるPivotを使うほうがいいと思いますね。

---