select
case when layername ='(MU)コア(上流側)' THEN SUM(Soil) else 0 END as [コア],
case when layername ='(MU)フィルター(下流側)' THEN SUM(Soil) else 0 END as [下流フィルター],
case when layername ='(MU)リップラップ(上流側)' THEN SUM(Soil) else 0 END as [上流リップラップ],
case when layername ='(MU)ロック①(上流側)' THEN SUM(Soil) else 0 END as [上流ロック①],
case when layername ='(MU)ロック②(下流側)' THEN SUM(Soil) else 0 END as [下流ロック②],
case when layername ='(MU)フィルター(上流側)' THEN SUM(Soil) else 0 END as [上流フィルター],
case when layername ='(MU)リップラップ(下流側)' THEN SUM(Soil) else 0 END as [下流リップラップ],
case when layername ='(MU)ロック①(下流側)' THEN SUM(Soil) else 0 END as [下流ロック①],
昼夜,crdate from
(
select (case when (to_char(crdate,'hh24' )>=c.HIRU and to_char(crdate,'hh24' )<=c.YORU) then '昼' else '夜' end) as 昼夜,
souatsu*0.5*0.5 as Soil, b.layername,to_char(a.crdate,'yyyy-MM-dd') as crdate from hiruyoruset c,doryo a
left join zairyoinfo b
on a.zairyou=b.zairyou
where b.zairyou in (0,1,2,3,4,6,7,8)
) table_a
group by 昼夜,crdate,layername
order by crdate
select
case when layername ='(MU)??(上流側)' THEN SUM(Soil) else 0 END as "[コア]",
case when layername ='(MU)?????(下流側)' THEN SUM(Soil) else 0 END as "[下流フィルター]",
case when layername ='(MU)????????(上流側)' THEN SUM(Soil) else 0 END as "[上流リップラップ]",
case when layername ='(MU)???①(上流側)' THEN SUM(Soil) else 0 END as "[上流ロック①]",
case when layername ='(MU)???②(下流側)' THEN SUM(Soil) else 0 END as "[下流ロック②]",
case when layername ='(MU)?????(上流側)' THEN SUM(Soil) else 0 END as "[上流フィルター]",
case when layername ='(MU)????????(下流側)' THEN SUM(Soil) else 0 END as "[下流リップラップ]",
case when layername ='(MU)???①(下流側)' THEN SUM(Soil) else 0 END as "[下流ロック①]",
昼夜,crdate
from
(
select (case when (to_char(crdate,'hh24' )>=c.HIRU and to_char(crdate,'hh24' )<=c.YORU) then '昼' else '夜' end) as 昼夜,
souatsu*0.5*0.5 as Soil, b.layername,to_char(a.crdate,'yyyy-MM-dd') as crdate from hiruyoruset c,doryo a
left join zairyoinfo b
on a.zairyou=b.zairyou
where b.zairyou in (0,1,2,3,4,6,7,8)
) table_a
group by 昼夜,crdate,layername
order by crdate