关于oracle熟手数据 显示成 列数据

关于oracle内行数据 显示成 列数据
表数据
cod_mitem rot_ord plan_wkhr_m plan_wkhr_no status Z12009-M01 15 20.000 3 02
Z12009-M01 15 20.000 00
Z12009-M01 15 20.000 2 01 

求改写成


cod_mitem rot_ord plan_wkhr_m plan_wkhr_no status00 status01 status02Z12009-M01 15 20.000 5 2 3


自己写的代码，，一直错误

SQL code

select cod_mitem ,rot_ord ,plan_wkhr_m ,sum(plan_wkhr_no),       case when status = '00' then plan_wkhr_no else 0 end  审核中,       case when status = '01' then plan_wkhr_no else 0 end  通过审核,       case when status = '02' then plan_wkhr_no else 0 end  退审from(select cod_mitem,rot_ord,plan_wkhr_m,sum(plan_wkhr_n)+sum(plan_wkhr_o) as plan_wkhr_no,status from (select c.cod_mitem,b.rot_ord,b.Plan_wkhr_m,a.plan_wkhr_n,a.plan_wkhr_o,a.start_date,a.status from web_wlms_o4sequence_user a        left join web_wlms_o3sequence b on a.rot_ord_id = b.id       left join web_wlms_o2order c on b.num_ord_id = c.id       where to_char(a.start_date,'yyyymmdd')>=(select to_char(sysdate,'yyyymmdd') from dual) ) group by cod_mitem,rot_ord,plan_wkhr_m,status) group by cod_mitem,rot_ord,plan_wkhr_m

SQL code

with web_wlms_o4sequence_user(cod_mitem, rot_ord, plan_wkhr_m, plan_wkhr_no, status)as (          select 'Z12009-M01', 15, '20.000', 3, '02' from dualunion all select 'Z12009-M01', 15, '20.000', 5, '00' from dualunion all select 'Z12009-M01', 15, '20.000', 2, '01' from dual)select cod_mitem, rot_ord, plan_wkhr_m,max(decode(status, '00', plan_wkhr_no)) status00,max(decode(status, '01', plan_wkhr_no)) status01,max(decode(status, '02', plan_wkhr_no)) status02from web_wlms_o4sequence_user group by cod_mitem, rot_ord, plan_wkhr_m;
[解决办法]
SQL code
with web_wlms_o4sequence_user(cod_mitem, rot_ord, plan_wkhr_m, plan_wkhr_no, status)as (          select 'Z12009-M01', 01, '10.000', 7, '01' from dualunion all select 'Z12009-M01', 14, '20.000', 13, '01' from dualunion all select 'Z12009-M01', 15, '20.000', 3, '02' from dualunion all select 'Z12009-M01', 15, '20.000', 2, '00' from dualunion all select 'Z12009-M01', 15, '20.000', 2, '01' from dualunion all select 'Z12009-M01', 15, '20.000', 6, '01' from dual)select cod_mitem 工程机号, rot_ord 工程令次, plan_wkhr_m 令次分配工时,sum(plan_wkhr_no) 令次总派工时,sum(decode(status, '00', plan_wkhr_no)) 审核中工时,sum(decode(status, '01', plan_wkhr_no)) 审核通过,sum(decode(status, '02', plan_wkhr_no)) 退审工时from web_wlms_o4sequence_user group by cod_mitem, rot_ord, plan_wkhr_morder by 1,2,3;--执行结果工程机号   工程令次         令次分配工时 令次总派工时           审核中工时             审核通过               退审工时---------- ---------------- ------------ ---------------------- ---------------------- ---------------------- --------Z12009-M01 1                      10.000 7                                             7                              Z12009-M01 14                     20.000 13                                            13                             Z12009-M01 15                     20.000 13                     2                      8                      3