create table BreakTest(ID,ColA,ColB,ColC,ColD,ColE) as
select 1,'AAAA',9999,'cccc',9999,'AAAA' from dual union
select 2,'AAAA',5555,'eeee',9999,'AAAA' from dual union
select 3,'HHHH',3333,'eeee',9999,'BBBB' from dual union
select 4,'FFFF',4444,'cccc',9999,'BBBB' from dual union
select 5,'FFFF',4444,'cccc',1111,'CCCC' from dual;
SQL
--■■■withで中間表を作る方法■■■
with Work as (
select ID,ColA,ColB,ColC,ColD,ColE,
count(ColA) over(order by ID) as ColACount,
count(ColB) over(order by ID) as ColBCount,
count(ColC) over(order by ID) as ColCCount,
count(ColD) over(order by ID) as ColDCount,
count(ColE) over(order by ID) as ColECount
from (select ID,
nullif(ColA,Lag(ColA) over(order by ID)) as ColA,
nullif(ColB,Lag(ColB) over(order by ID)) as ColB,
nullif(ColC,Lag(ColC) over(order by ID)) as ColC,
nullif(ColD,Lag(ColD) over(order by ID)) as ColD,
nullif(ColE,Lag(ColE) over(order by ID)) as ColE
from BreakTest))
select m.ID,a.ColA,b.ColB,c.ColC,d.ColD,e.ColE
from BreakTest m
Left Join Work a on m.ID = a.ColACount and a.ColA is not null
Left Join Work b on m.ID = b.ColBCount and b.ColB is not null
Left Join Work c on m.ID = c.ColCCount and c.ColC is not null
Left Join Work d on m.ID = d.ColDCount and d.ColD is not null
Left Join Work e on m.ID = e.ColECount and e.ColE is not null
where a.ColA is not null
or b.ColB is not null
or c.ColC is not null
or d.ColD is not null
or e.ColE is not null
order by ID;
--■■■rangeを使う方法■■■
select ID,ColA,ColB,ColC,ColD,ColE
from (select ID,
case when ID <= sum(FlagA) over()
then max(ColA) over(order by SumA
range between ID-SumA following
and ID-SumA following) end as ColA,
case when ID <= sum(FlagB) over()
then max(ColB) over(order by SumB
range between ID-SumB following
and ID-SumB following) end as ColB,
case when ID <= sum(FlagC) over()
then max(ColC) over(order by SumC
range between ID-SumC following
and ID-SumC following) end as ColC,
case when ID <= sum(FlagD) over()
then max(ColD) over(order by SumD
range between ID-SumD following
and ID-SumD following) end as ColD,
case when ID <= sum(FlagE) over()
then max(ColE) over(order by SumE
range between ID-SumE following
and ID-SumE following) end as ColE
from (select ID,ColA,ColB,ColC,ColD,ColE,
FlagA,FlagB,FlagC,FlagD,FlagE,
case FlagA when 1 then sum(FlagA) over(order by ID) else 0 end as SumA,
case FlagB when 1 then sum(FlagB) over(order by ID) else 0 end as SumB,
case FlagC when 1 then sum(FlagC) over(order by ID) else 0 end as SumC,
case FlagD when 1 then sum(FlagD) over(order by ID) else 0 end as SumD,
case FlagE when 1 then sum(FlagE) over(order by ID) else 0 end as SumE
from (select ID,ColA,ColB,ColC,ColD,ColE,
case when Lag(ColA) over(order by ID) = ColA then 0 else 1 end as FlagA,
case when Lag(ColB) over(order by ID) = ColB then 0 else 1 end as FlagB,
case when Lag(ColC) over(order by ID) = ColC then 0 else 1 end as FlagC,
case when Lag(ColD) over(order by ID) = ColD then 0 else 1 end as FlagD,
case when Lag(ColE) over(order by ID) = ColE then 0 else 1 end as FlagE
from BreakTest)))
where ColA is not null
or ColB is not null
or ColC is not null
or ColD is not null
or ColE is not null
order by ID;
解説