sql之行列转换备份

select bustype, tx.anshi,tx.chaoshi,tx.weibanjie,(CASE WHEN (tx.anshi+tx.chaoshi+tx.weibanjie) = 0 THEN 0
        ELSE  round(tx.anshi*100/(tx.anshi+tx.chaoshi+tx.weibanjie),2)
        END) bjlv from (






        select distinct bustype
        ,sum(decode(lx,'anshi',counts,0)) anshi
        ,sum(decode(lx,'chaoshi',counts,0)) chaoshi
        ,sum(decode(lx,'weibanjie',counts,0)) weibanjie


        from (


        select * from (
        select substr(paramcode, 1, 2) as bustype,
        count(bus_type) as counts,
        'anshi' as lx
        from (
        select s.paramcode,oob.bus_type, row_number() OVER(PARTITION BY s.ordernums ORDER BY s.ordernums) asas
        from dicall s left join (
        select *
        from (select ob.app_type,ob.bus_type,
        row_number() OVER(PARTITION BY ob.id ORDER BY ob.id) n
        from
        EN_ACCEPTEDRESULT ob
        left join (select t.paramcode  paramcode1,
        t.paramdesc  paramdesc1,
        t2.paramcode paramcode2,
        t2.paramdesc paramdesc2
        from (select *
        from dicall
        where paramtype = 'tbenapptradedic') t,
        (select *
        from dicall
        where paramtype = 'tbenapptypedic') t2) t3
        on ob.app_type = t3.paramcode1
        and ob.bus_type = t3.paramcode2


        left join


        MONITORRULES m
        on m.businessenterprise = ob.app_type
        and ob.bus_type = m.typeofservice
        where ob.isover = '1'


         


         
        and to_date(ob.accept_time,'yyyy-mm-dd hh24:mi:ss')+to_number(m.daymonitor) >= to_date(ob.approval_date,'yyyy-mm-dd hh24:mi:ss')        


         
         
        ) where n = 1)  oob on s.paramcode= oob.bus_type where s.paramtype='tbenapptypedic'  )


        group by substr(paramcode, 1, 2)    )




        union all
        select * from (
        select substr(paramcode, 1, 2) as bustype,
        count(bus_type) as counts,
        'chaoshi' as lx
        from (
        select s.paramcode,oob.bus_type, row_number() OVER(PARTITION BY s.ordernums ORDER BY s.ordernums) asas
        from dicall s left join (
        select *
        from (select ob.app_type,ob.bus_type,
        row_number() OVER(PARTITION BY ob.id ORDER BY ob.id) n
        from
        EN_ACCEPTEDRESULT ob
        left join (select t.paramcode  paramcode1,
        t.paramdesc  paramdesc1,
        t2.paramcode paramcode2,
        t2.paramdesc paramdesc2
        from (select *
        from dicall
        where paramtype = 'tbenapptradedic') t,
        (select *
        from dicall
        where paramtype = 'tbenapptypedic') t2) t3
        on ob.app_type = t3.paramcode1
        and ob.bus_type = t3.paramcode2


        left join


        MONITORRULES m
        on m.businessenterprise = ob.app_type
        and ob.bus_type = m.typeofservice
        where ob.isover = '1'


         






          and to_date(ob.accept_time,'yyyy-mm-dd hh24:mi:ss')+to_number(m.daymonitor) < to_date(ob.approval_date,'yyyy-mm-dd hh24:mi:ss')  
         
         


        ) where n = 1)  oob on s.paramcode= oob.bus_type where s.paramtype='tbenapptypedic'  )


        group by substr(paramcode, 1, 2)    )
        union all
        select * from (
        select substr(paramcode, 1, 2) as bustype,
        count(bus_type) as counts,
        'weibanjie' as lx
        from (
        select s.paramcode,oob.bus_type, row_number() OVER(PARTITION BY s.ordernums ORDER BY s.ordernums) asas
        from dicall s left join (
        select *
        from (select ob.app_type,ob.bus_type,
        row_number() OVER(PARTITION BY ob.id ORDER BY ob.id) n
        from
        EN_ACCEPTEDRESULT ob
        left join (select t.paramcode  paramcode1,
        t.paramdesc  paramdesc1,
        t2.paramcode paramcode2,
        t2.paramdesc paramdesc2
        from (select *
        from dicall
        where paramtype = 'tbenapptradedic') t,
        (select *
        from dicall
        where paramtype = 'tbenapptypedic') t2) t3
        on ob.app_type = t3.paramcode1
        and ob.bus_type = t3.paramcode2


        left join


        MONITORRULES m
        on m.businessenterprise = ob.app_type
        and ob.bus_type = m.typeofservice
        where ob.isover = '0'
         




         
        and to_date(ob.accept_time,'yyyy-mm-dd hh24:mi:ss')+to_number(m.daymonitor) < to_date(ob.approval_date,'yyyy-mm-dd hh24:mi:ss')
            
         
         




        ) where n = 1)  oob on s.paramcode= oob.bus_type where s.paramtype='tbenapptypedic'  )


        group by substr(paramcode, 1, 2)    )




        )group by bustype   ) tx order by tx.bustype