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;
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;