select gh ,xm ,
max(A.bz) as bz ,
max(A.jcz) as jcz ,
max(A.dl) as dl ,
max(A.czzx) as czzx ,
max(A.jt) as jt ,
max(A.dt) as dt ,
max(A.jl) as jl ,
max(A.zycqbw) as zycqbw ,
max(A.zc) as zc ,
max(A.zxqbw) as zxqbw ,
max(A.lczx) as lczx
from(
select gh ,xm ,
CASE bgzlmc when '编组'then sum(cworkcount) else 0 end as bz ,
CASE bgzlmc when '解车组'then sum(cworkcount) else 0 end as jcz ,
CASE bgzlmc when '代量'then sum(cworkcount) else 0 end as dl ,
CASE bgzlmc when '车组转线'then sum(cworkcount) else 0 end as czzx ,
CASE bgzlmc when '解体'then sum(cworkcount) else 0 end as jt ,
CASE bgzlmc when '倒途'then sum(cworkcount) else 0 end as dt ,
CASE bgzlmc when '禁溜'then sum(cworkcount) else 0 end as jl ,
CASE bgzlmc when '作业车取摆位'then sum(cworkcount) else 0 end as zycqbw ,
CASE bgzlmc when '转场'then sum(cworkcount) else 0 end as zc ,
CASE bgzlmc when '站修取摆位'then sum(cworkcount) else 0 end as zxqbw ,
CASE bgzlmc when '列车转线'then sum(cworkcount) else 0 end as lczx
from t_dcd_confirmworkdetail left join t_dcd_personworkdetails
on t_dcd_confirmworkdetail.planid = t_dcd_personworkdetails.planid
left join t_dcd_bgzl on t_dcd_confirmworkdetail.bgzlid =t_dcd_bgzl.id
where 1=1 and ctime between '2018-09-01' and '2018-09-19' group by gh, xm,bgzlmc )A group by gh,xm
转换前数据：
转换后数据：
