Preparing dummy data:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
truncate table sales; drop table sales; create global temporary table sales ( store number (2,0) ,department varchar2(10) ,sale_date date ,item_id varchar2(10) ,amount number(12,2) ) on commit preserve rows ; commit; insert into sales values (1, 'laptops', to_date('01/05/2012', 'DD/MM/YYYY'), 'sn12356', 1452); insert into sales values (2, 'laptops', to_date('01/05/2012', 'DD/MM/YYYY'), 'sn12356', 1452); insert into sales values (2, 'laptops', to_date('02/05/2012', 'DD/MM/YYYY'), 'sn12358', 1820); insert into sales values (1, 'desktops', to_date('01/05/2012', 'DD/MM/YYYY'), 'sn12351', 2580); insert into sales values (1, 'desktops', to_date('03/05/2012', 'DD/MM/YYYY'), 'sn12359', 1300); insert into sales values (2, 'parts', to_date('01/05/2012', 'DD/MM/YYYY'), 'sn12352', 345); insert into sales values (1, 'tablets', to_date('01/05/2012', 'DD/MM/YYYY'), 'sn12353', 680); insert into sales values (1, 'parts', to_date('02/05/2012', 'DD/MM/YYYY'), 'sn12354', 62); insert into sales values (1, 'parts', to_date('03/05/2012', 'DD/MM/YYYY'), 'sn12355', 230); insert into sales values (2, 'tablets', to_date('01/05/2012', 'DD/MM/YYYY'), 'sn12316', 540); insert into sales values (1, 'tablets', to_date('04/05/2012', 'DD/MM/YYYY'), 'sn12326', 910); insert into sales values (1, 'parts', to_date('01/05/2012', 'DD/MM/YYYY'), 'sn12336', 220); insert into sales values (2, 'phones', to_date('04/05/2012', 'DD/MM/YYYY'), 'sn12346', 650); insert into sales values (1, 'phones', to_date('03/05/2012', 'DD/MM/YYYY'), 'sn12366', 690); insert into sales values (2, 'servers', to_date('03/05/2012', 'DD/MM/YYYY'), 'sn12376', 5800); insert into sales values (2, 'laptops', to_date('01/05/2012', 'DD/MM/YYYY'), 'sn12156', 1450); insert into sales values (1, 'laptops', to_date('03/05/2012', 'DD/MM/YYYY'), 'sn12256', 1280); insert into sales values (2, 'laptops', to_date('02/05/2012', 'DD/MM/YYYY'), 'sn12458', 1620); insert into sales values (1, 'desktops', to_date('02/05/2012', 'DD/MM/YYYY'), 'sn15351', 2780); insert into sales values (2, 'desktops', to_date('03/05/2012', 'DD/MM/YYYY'), 'sn16359', 1400); insert into sales values (1, 'parts', to_date('04/05/2012', 'DD/MM/YYYY'), 'sn12752', 350); insert into sales values (1, 'tablets', to_date('04/05/2012', 'DD/MM/YYYY'), 'sn18353', 580); insert into sales values (1, 'parts', to_date('02/05/2012', 'DD/MM/YYYY'), 'sn19354', 568); insert into sales values (2, 'parts', to_date('03/05/2012', 'DD/MM/YYYY'), 'sn11355', 25); insert into sales values (1, 'tablets', to_date('01/05/2012', 'DD/MM/YYYY'), 'sn11316', 560); insert into sales values (2, 'tablets', to_date('04/05/2012', 'DD/MM/YYYY'), 'sn11326', 750); insert into sales values (2, 'parts', to_date('01/05/2012', 'DD/MM/YYYY'), 'sn14336', 90); insert into sales values (2, 'phones', to_date('04/05/2012', 'DD/MM/YYYY'), 'sn15346', 450); insert into sales values (2, 'phones', to_date('03/05/2012', 'DD/MM/YYYY'), 'sn16366', 790); insert into sales values (2, 'servers', to_date('04/05/2012', 'DD/MM/YYYY'), 'sn17376', 9990); commit; |
Pivoting:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 |
---------------------------------------------------------------- ------------- building dynamic pivot table 1 -------------------- ---------------------------------------------------------------- -- truncate table pivot_tmp; drop table pivot_tmp; truncate table pivot_01; drop table pivot_01; commit; -- declare pivot_column sales.department%type; -- pivot_string varchar2(4000); total_col_string varchar2(4000); total_row_string varchar2(4000); i int; cursor curs_1 is select distinct department -- from sales -- order by department; -- begin pivot_string := '('; total_col_string := '('; total_row_string := ''; i := 0; open curs_1; loop fetch curs_1 into pivot_column; exit when curs_1%notfound; if i != 0 then pivot_string := pivot_string || ', '; total_col_string := total_col_string || ' + '; total_row_string := total_row_string || ', '; end if; pivot_string := pivot_string || '''' || pivot_column || '''' || ' as ' || '"' || pivot_column || '"'; total_col_string := total_col_string || 'nvl("' || pivot_column || '", 0)'; total_row_string := total_row_string || 'sum("' || pivot_column || '") as "' || pivot_column || '"'; i := i + 1; end loop; close curs_1; pivot_string := pivot_string || ', 1 as "Total")'; total_col_string := total_col_string || ')'; total_row_string := total_row_string || ', sum("Total") as "Total"'; dbms_output.put_line('pivot string:'); dbms_output.put_line(pivot_string); dbms_output.put_line('total column string:'); dbms_output.put_line(total_col_string); dbms_output.put_line('total row string:'); dbms_output.put_line(total_row_string); execute immediate -- 'create global temporary table pivot_tmp on commit preserve rows as select * from ( select store ,sale_date ,department ,amount from sales ) pivot ( sum(amount) for department in ' || pivot_string || ') order by store ,sale_date'; commit; execute immediate 'update pivot_tmp set "Total" = ' || total_col_string; commit; execute immediate 'create global temporary table pivot_01 on commit preserve rows as select nvl(to_char(store), ''Grand Total'') as store ,nvl(to_char(sale_date), ''------'') as sale_date ,' || total_row_string || 'from pivot_tmp group by rollup ( store ,sale_date )'; commit; execute immediate 'update pivot_01 set store = store || '' Total'' where sale_date = ''------'' and store <> ''Grand Total'' '; commit; execute immediate 'truncate table pivot_tmp'; execute immediate 'drop table pivot_tmp'; commit; end; / --------------- end of dynamic pivot table 1 -------------------- |