Oracle SQL: Multiple pivots

Question

I have the following schema and built a query with 2 pivots sqlfiddle

select * from 
    (select * from 
        (select order_id, product_id, adl_qty, kid_qty, status, 
        case 
            when status = 'DUE OUT' and package_name = 'BRF' then 'BRF' 
            when status = 'ARRIVAL' and package_name IN ('LNC', 'DNR') then package_name 
            when status = 'STAY OVER' and package_name IN ('BRF', 'LNC', 'DNR') then package_name 
            --else package_name 
        end  
        adl_package, 

        case 
            when status = 'DUE OUT' and package_name = 'KIDBRF' then 'KIDBRF' 
            when status = 'ARRIVAL' and package_name IN ('KIDLNC', 'KIDDNR') then package_name 
            when status = 'STAY OVER' and package_name IN ('KIDBRF', 'KIDLNC', 'KIDDNR') then package_name 
            --else package_name 
        end  
        kid_package 

        from orders 
        left join product_packages using (product_id) 
        left join packages using (package_id) 
        order by status, adl_package) 
    pivot  
    ( 
        max(adl_qty)  
        for adl_package in ('BRF' brf, 'LNC' lnc, 'DNR' dnr)  
    ) 
    ) 
    pivot 
    ( 
        max(kid_qty)  
        for kid_package in ('KIDBRF' kidbrf, 'KIDLNC' kidlnc, 'KIDDNR' kiddnr)  
    ) 
    order by product_id

The problem is each pivot creates one row (2 rows for each order_id), I actually want to join 2 pivots in 1 row. The result should look like this:

ORDER_ID  PRODUCT_ID  STATUS    BRF  LNC  DNR  KIDBRF  KIDLNC  KIDDNR
--------  ----------  ------    ---  ---  ---  ------  ------  ------
       1           2  DUE OUT     1                                  
       2           3  ARRIVAL          2    2               1       1
       3           3  STAY OVER   1    1    1       2       2       2

Thank you of your help.

Answer 1

Use conditional aggregation instead of pivot:

select order_id, product_id status,
       sum(case when adl_package = 'BRF' then adl_qty else 0 end) as brf,
       sum(case when adl_package = 'LNC' then adl_qty else 0 end) as lnc,
       sum(case when adl_package = 'DNR' then adl_qty else 0 end) as dnr,
       sum(case when kid_package = 'KIDBRF' then kid_qty else 0 end) as kidbrf,
       sum(case when kid_package = 'KIDLNC' then kid_qty else 0 end) as kidlnc,
       sum(case when kid_package = 'KIDDNR' then kid_qty else 0 end) as kiddnr
from orders o left join
     product_packages pp
     using (product_id) left join
     packages p
     using (package_id) 
group by order_id, product_id status;

This uses sum() instead of max()`.