Forum Stats

  • 3,854,194 Users
  • 2,264,340 Discussions
  • 7,905,598 Comments

Discussions

Using multiple pivots

3792435
3792435 Member Posts: 2
edited Oct 17, 2018 2:42PM in SQL & PL/SQL

Hello,

I am fairly new to Oracle PL/SQL.

I have the following schema: http://sqlfiddle.com/#!4/cae7c/2/0

CREATE TABLE products  (product_id NUMBER(5) NOT NULL,   product_name VARCHAR2(10) NOT NULL,   CONSTRAINT product_pk PRIMARY KEY (product_id)  );CREATE TABLE packages  (package_id NUMBER(5) NOT NULL,   package_name VARCHAR2(10) NOT NULL,   CONSTRAINT package_pk PRIMARY KEY (package_id)  );CREATE TABLE product_packages  (product_id NUMBER(5) NOT NULL,   package_id NUMBER(5) NOT NULL,   CONSTRAINT product_fk FOREIGN KEY (product_id) REFERENCES products(product_id),   CONSTRAINT package_fk FOREIGN KEY (package_id) REFERENCES packages(package_id)  );CREATE TABLE customers  (customer_id NUMBER(10) NOT NULL,   customer_name VARCHAR2(50) NOT NULL,   CONSTRAINT customer_pk PRIMARY KEY (customer_id)  );CREATE TABLE orders  (order_id NUMBER(5) NOT NULL,   customer_id NUMBER(10) NOT NULL,   product_id NUMBER(5) NOT NULL,   adl_qty NUMBER(5) NOT NULL,   kid_qty NUMBER(5),  status VARCHAR2(10),  CONSTRAINT ord_customer_fk FOREIGN KEY (customer_id) REFERENCES customers(customer_id),   CONSTRAINT ord_product_fk FOREIGN KEY (product_id) REFERENCES products(product_id)  );INSERT  INTO products (product_id, product_name) VALUES (1, 'BARBB');INSERT  INTO products (product_id, product_name) VALUES (2, 'BARHB');INSERT  INTO products (product_id, product_name) VALUES (3, 'BARFB');INSERT  INTO packages (package_id, package_name) VALUES (1, 'BRF');INSERT  INTO packages (package_id, package_name) VALUES (2, 'LNC');INSERT  INTO packages (package_id, package_name) VALUES (3, 'DNR');INSERT  INTO packages (package_id, package_name) VALUES (4, 'KIDBRF');INSERT  INTO packages (package_id, package_name) VALUES (5, 'KIDLNC');INSERT  INTO packages (package_id, package_name) VALUES (6, 'KIDDNR');INSERT  INTO product_packages (product_id, package_id) VALUES (1, 1);INSERT  INTO product_packages (product_id, package_id) VALUES (1, 4);INSERT  INTO product_packages (product_id, package_id) VALUES (2, 1);INSERT  INTO product_packages (product_id, package_id) VALUES (2, 4);INSERT  INTO product_packages (product_id, package_id) VALUES (2, 3);INSERT  INTO product_packages (product_id, package_id) VALUES (2, 6);INSERT  INTO product_packages (product_id, package_id) VALUES (3, 1);INSERT  INTO product_packages (product_id, package_id) VALUES (3, 2);INSERT  INTO product_packages (product_id, package_id) VALUES (3, 3);INSERT  INTO product_packages (product_id, package_id) VALUES (3, 4);INSERT  INTO product_packages (product_id, package_id) VALUES (3, 5);INSERT  INTO product_packages (product_id, package_id) VALUES (3, 6);INSERT  INTO customers (customer_id, customer_name) VALUES (1, 'John Smith');INSERT  INTO customers (customer_id, customer_name) VALUES (2, 'Adam Nash');INSERT  INTO customers (customer_id, customer_name) VALUES (3, 'Brain Tracy');INSERT  INTO orders (order_id, customer_id, product_id, adl_qty, kid_qty, status) VALUES (1, 1, 2, 1, 0, 'DUE OUT');INSERT  INTO orders (order_id, customer_id, product_id, adl_qty, kid_qty, status) VALUES (2, 2, 3, 2, 1, 'ARRIVAL');INSERT  INTO orders (order_id, customer_id, product_id, adl_qty, kid_qty, status) VALUES (3, 3, 3, 1, 2, 'STAY OVER');

Oracle DB version is 11.2.0.1

I am trying to use multiple pivots and this is what i accomplished so far

select * from (select * from     (select 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 result is :

PRODUCT_IDSTATUSBRFLNCDNRKIDBRFKIDLNCKIDDNR
2DUE OUT1(null)(null)(null)(null)(null)
2DUE OUT(null)(null)(null)0(null)(null)
3ARRIVAL(null)22(null)(null)(null)
3ARRIVAL(null)(null)(null)(null)11
3STAY OVER111(null)(null)(null)
3STAY OVER(null)(null)(null)222

As you can see there 2 rows for each order_id, I want one row for each order id; like

PRODUCT_IDSTATUSBRFLNCDNRKIDBRFKIDLNCKIDDNR
2DUE OUT1(null)(null)0(null)(null)
3ARRIVAL(null)22(null)11
3STAY OVER111222

Please help me out how I can do this.

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Oct 17, 2018 11:46AM
    3792435 wrote:Hello,I am fairly new to Oracle PL/SQL.I have the following schema: http://sqlfiddle.com/#!4/cae7c/2/0Oracle DB version is 11gI am trying to use multiple pivots and this is what i accomplished so far
    1. select*from
    2. (select*from
    3. (selectproduct_id,adl_qty,kid_qty,status,
    4. case
    5. whenstatus='DUEOUT'andpackage_name='BRF'then'BRF'
    6. whenstatus='ARRIVAL'andpackage_nameIN('LNC','DNR')thenpackage_name
    7. whenstatus='STAYOVER'andpackage_nameIN('BRF','LNC','DNR')thenpackage_name
    8. --elsepackage_name
    9. end
    10. adl_package,
    11. case
    12. whenstatus='DUEOUT'andpackage_name='KIDBRF'then'KIDBRF'
    13. whenstatus='ARRIVAL'andpackage_nameIN('KIDLNC','KIDDNR')thenpackage_name
    14. whenstatus='STAYOVER'andpackage_nameIN('KIDBRF','KIDLNC','KIDDNR')thenpackage_name
    15. --elsepackage_name
    16. end
    17. kid_package
    18. fromorders
    19. leftjoinproduct_packagesusing(product_id)
    20. leftjoinpackagesusing(package_id)
    21. orderbystatus,adl_package)
    22. pivot
    23. (
    24. max(adl_qty)
    25. foradl_packagein('BRF'brf,'LNC'lnc,'DNR'dnr)
    26. )
    27. )
    28. pivot
    29. (
    30. max(kid_qty)
    31. forkid_packagein('KIDBRF'kidbrf,'KIDLNC'kidlnc,'KIDDNR'kiddnr)
    32. )
    33. orderbyproduct_id;
    select * from  (select * from   (select 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 result is :PRODUCT_IDSTATUSBRFLNCDNRKIDBRFKIDLNCKIDDNR2DUE OUT1(null)(null)(null)(null)(null)2DUE OUT(null)(null)(null)0(null)(null)3ARRIVAL(null)22(null)(null)(null)3ARRIVAL(null)(null)(null)(null)113STAY OVER111(null)(null)(null)3STAY OVER(null)(null)(null)222As you can see there 2 rows for each order_id, I want one row for each order id; like for order_id = 3, it will be3STAY OVER111222Please help me out how I can do this.

    Please click on URL below & respond accordingly

    Re: 2. How do I ask a question on the forums?

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,501 Red Diamond
    edited Oct 17, 2018 11:49AM

    Hi,

    3792435 wrote:...I have the following schema: http://sqlfiddle.com/#!4/cae7c/2/0...

    Post the sample data, desired results and explanation right in this forum.

    Oracle DB version is 11g

    Always give your full, exact version, e.g. 11.2.0.2.0.  There were major changes between versions 11.1 and 11.2 (though they might not be relevant to this problem).

  • 3792435
    3792435 Member Posts: 2
    edited Oct 17, 2018 1:38PM
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,501 Red Diamond
    edited Oct 17, 2018 2:39PM

    Hi,

    3792435 wrote:did all that

    It's very confusing when you make changes to existing posts, especially after people have replied to them.  Also, it's very likely that nobody will notice you made a change, and therefore not reply at all.  Make corrections and additions in a new reply.

    Instead of doing two PIVOTs, you can do an UNPIVOT and a PIVOT, like this:

    WITH    data_to_unpivot    AS(    select 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       end        kid_package                 from orders       join product_packages using (product_id)       join packages using (package_id)   )SELECT    product_id, status     -- that is, all columns except age_grp,            brf,    lnc,    dnr,         kidbrf, kidlnc, kiddnrFROM      data_to_unpivotUNPIVOT   (    (qty, package)          FOR  age_grp  IN ( (adl_qty, adl_package)  AS '-'                           , (kid_qty, kid_package)  AS '-'                           )          )PIVOT     (    MAX (qty)          FOR  package  IN ( 'BRF'     AS brf                           , 'LNC'     AS lnc                           , 'DNR'     AS dnr                           , 'KIDBRF'  AS kidbrf                           , 'KIDLNC'  AS kidlnc                           , 'KIDDNR'  AS kiddnr                           )          )ORDER BY  product_id, status;

    Notice that the sub-query called data_to_unpivot is very much like an in-line view you posted.  The only changes I made were to change the joins to inner joins (the foreign key constraints guarantee that inner and outer joins are equivalent here) and removed the ORDER BY clause (ORDER BY in a sub-query is usually pointless).

    Output from your sample data:

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

    There could be ways to make this a little simpler and more efficient.  For example, if the orders data was unpivoted earlier, you wouldn't need two almost identical CASE expressions.  I don't know enough about your data or your requirements to be sure.

  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Oct 17, 2018 2:42PM
    select order_id,  product_id, kidbrf_a kidbrf, kidlnc_a kidlnc, kiddnr_a kiddnr, brf_b brf, lnc_b lnc, dnr_b dnr from (  select order_id, product_id, status, val, adl_qty, kid_qty   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)   )  unpivot  (    val for col in (adl_package, kid_package)  ) )pivot  (     max(adl_qty) a, max(kid_qty)  b     for val     in ('KIDBRF' kidbrf, 'KIDLNC' kidlnc, 'KIDDNR' kiddnr, 'BRF' brf, 'LNC' lnc, 'DNR' dnr) ) order by product_id;;  ORDER_ID PRODUCT_ID     KIDBRF     KIDLNC     KIDDNR        BRF        LNC        DNR---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------         1          2          1                                0                               2          3                     2          2                     1          1         3          3          1          1          1          2          2          2