create table modet(Skey,Col1,Col2,Col3) as
select 1,'1aaa',null ,null from dual union all
select 2,'1bbb',null ,'3xxx' from dual union all
select 3,'1ccc',null ,'3yyy' from dual union all
select 4,null ,'2aaa','3zzz' from dual union all
select 5,null ,'2bbb',null from dual union all
select 6,null ,'2ccc',null from dual union all
select 7,null ,'2ddd',null from dual;
--■■■model句を使う方法(10以降)■■■
select Skey,Col1,Col2,Col3
from (select Skey,Col1,Col2,Col3
from modeT
model
dimension by(Skey,
nvl2(Col1,Row_Number() over(order by Col1),0) as rn1,
nvl2(Col2,Row_Number() over(order by Col2),0) as rn2,
nvl2(Col3,Row_Number() over(order by Col3),0) as rn3)
measures(Col1,Col2,Col3)
rules(
Col1[any,any,any,any] order by Skey= max(Col1)[any,CV(Skey),any,any],
Col2[any,any,any,any] order by Skey= max(Col2)[any,any,CV(Skey),any],
Col3[any,any,any,any] order by Skey= max(Col3)[any,any,any,CV(Skey)]))
where coalesce(Col1,Col2,Col3) is not null
order by Skey;
--■■■model句で参照modelを使う方法(10以降)■■■
select Skey,Col1,Col2,Col3
from (select Skey,Col1,Col2,Col3
from modet model
REFERENCE Col1model on
(select Col1,Row_Number() over(order by Col1) as rn1 from modet where Col1 is not null)
dimension by(rn1)
measures(Col1)
REFERENCE Col2model on
(select Col2,Row_Number() over(order by Col2) as rn2 from modet where Col2 is not null)
dimension by(rn2)
measures(Col2)
REFERENCE Col3model on
(select Col3,Row_Number() over(order by Col3) as rn3 from modet where Col3 is not null)
dimension by(rn3)
measures(Col3)
MAIN MAINmodel
dimension by(Skey)
measures(Col1,Col2,Col3)
rules(
Col1[any] = Col1model.Col1[cv(Skey)],
Col2[any] = Col2model.Col2[cv(Skey)],
Col3[any] = Col3model.Col3[cv(Skey)]))
where coalesce(Col1,Col2,Col3) is not null
order by Skey;
--■■■Range指定を使う方法■■■
select Skey,Col1,Col2,Col3
from (select Skey,
case when Skey <= Col1Rn
then max(Col1) over(order by Col1Rn
range between abs(Col1Rn-Skey) preceding
and abs(Col1Rn-Skey) preceding)
else max(Col1) over(order by Col1Rn
range between abs(Col1Rn-Skey) following
and abs(Col1Rn-Skey) following) end as Col1,
case when Skey <= Col2Rn
then max(Col2) over(order by Col2Rn
range between abs(Col2Rn-Skey) preceding
and abs(Col2Rn-Skey) preceding)
else max(Col2) over(order by Col2Rn
range between abs(Col2Rn-Skey) following
and abs(Col2Rn-Skey) following) end as Col2,
case when Skey <= Col3Rn
then max(Col3) over(order by Col3Rn
range between abs(Col3Rn-Skey) preceding
and abs(Col3Rn-Skey) preceding)
else max(Col3) over(order by Col3Rn
range between abs(Col3Rn-Skey) following
and abs(Col3Rn-Skey) following) end as Col3
from (select Skey,Col1,Col2,Col3,
Row_Number() over(order by Col1) as Col1Rn,
Row_Number() over(order by COL2) as Col2Rn,
Row_Number() over(order by COL3) as Col3Rn
from modet))
where coalesce(Col1,Col2,Col3) is not null
order by Skey;
--■■■Lead関数とLag関数を使う方法■■■
select Skey,Col1,Col2,Col3
from (select Skey,
case when Skey <= Col1Rn
then Lag (Col1,abs(Col1Rn-Skey)) over(order by Col1Rn)
else Lead(Col1,abs(Col1Rn-Skey)) over(order by Col1Rn) end as Col1,
case when Skey <= Col2Rn
then Lag (Col2,abs(Col2Rn-Skey)) over(order by Col2Rn)
else Lead(Col2,abs(Col2Rn-Skey)) over(order by Col2Rn) end as Col2,
case when Skey <= Col3Rn
then Lag (Col3,abs(Col3Rn-Skey)) over(order by Col3Rn)
else Lead(Col3,abs(Col3Rn-Skey)) over(order by Col3Rn) end as Col3
from (select Skey,Col1,Col2,Col3,
Row_Number() over(order by Col1) as Col1Rn,
Row_Number() over(order by COL2) as Col2Rn,
Row_Number() over(order by COL3) as Col3Rn
from modet))
where coalesce(Col1,Col2,Col3) is not null
order by Skey;
--■■■外部結合を使う方法■■■
select a.Skey,b.Col1,c.Col2,d.Col3
from modet a,
(select Col1,Row_Number() over(order by Col1) as rn from modet where Col1 is not null) b,
(select Col2,Row_Number() over(order by Col2) as rn from modet where Col2 is not null) c,
(select Col3,Row_Number() over(order by Col3) as rn from modet where Col3 is not null) d
where a.Skey = b.rn(+)
and a.Skey = c.rn(+)
and a.Skey = d.rn(+)
and coalesce(b.Col1,c.Col2,d.Col3) is not null
order by a.Skey;