关于oracle内行数据 显示成 列数据

Question

表数据
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
自己写的代码，，一直错误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

Answer 1

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 dual
union all select 'Z12009-M01', 15, '20.000', 5, '00' from dual
union 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)) status02
from web_wlms_o4sequence_user group by cod_mitem, rot_ord, plan_wkhr_m;输出结果
COD_MITEM  ROT_ORD                PLAN_WKHR_M STATUS00               STATUS01               STATUS02  
---------- ---------------------- ----------- ---------------------- ---------------------- ----------
Z12009-M01 15                     20.000      5                      2                      3         

Answer 2

已经查询出的表
cod_mitem    rot_ord     plan_wkhr_m    plan_wkhr_no    status   
Z12009-M01     01           10.000          7             01
Z12009-M01     14           20.000          13            01
Z12009-M01     15           20.000          3             02 
Z12009-M01     15           20.000          2             00
Z12009-M01     15           20.000          2             01
Z12009-M01     15           20.000          6             01              
转换成
(工程机号)  （工程令次）（令次分配工时）（令次总派工时） （审核中工时）（审核通过）  （退审工时）
cod_mitem     rot_ord    plan_wkhr_m     plan_wkhr_no       status00     status01     status02
Z12009-M01       01        10.000            7                             7              
Z12009-M01       14        20.000            13                            13  
Z12009-M01       15        20.000            13                  2          8              3

Answer 3

已经查询出的表
cod_mitem    rot_ord     plan_wkhr_m    plan_wkhr_no    status   
Z12009-M01     01           10.000          7             01
Z12009-M01     14           20.000          13            01
Z12009-M01     15           20.000          3             02 
Z12009-M01     15           20.000          2             00
Z12009-M01     15           20.000          2             01
Z12009-M01     15           20.000          6             01              
转换成
(工程机号)  （工程令次）（令次分配工时）（令次总派工时） （审核中工时）（审核通过）  （退审工时）
cod_mitem     rot_ord    plan_wkhr_m     plan_wkhr_no       status00     status01     status02
Z12009-M01       01        10.000            7                             7              
Z12009-M01       14        20.000            13                            13  
Z12009-M01       15        20.000            13                  2          8              3

Answer 4

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 dual
union all select 'Z12009-M01', 14, '20.000', 13, '01' from dual
union all select 'Z12009-M01', 15, '20.000', 3, '02' from dual
union all select 'Z12009-M01', 15, '20.000', 2, '00' from dual
union all select 'Z12009-M01', 15, '20.000', 2, '01' from dual
union 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_m
order 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       

Answer 5

表数据
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楼主给的表数据和结果形式确认吗？有错误吧。结果是否应该同1楼回复的结果？1楼答复的是标准行转列方法