Use SQL PIVOT to display a pivot table in Apex #JoelKallmanDay

In a project, we had the question on how we can visualize a pivot table in Apex. We have locations with warehouses, which we want to display as rows. In the columns, we needed the quantity of each stock item.

So our simple data model is:

To query all items with their stock, we use

select loc_id, loc_name, sto_quantity, item_name
from location, stock, item
where sto_loc_id_fk = loc_id
and sto_item_id_fk = item_id

Using this statement for an Interactive Report in Apex gives the following output:

We have several lines for the same item, eg. Tire B in Frankfurt. So we need to sum up per item and location.

Apex does not have a native feature to make a pivot table from this data, as this is already implemented with the SQL PIVOT. The following statement gives us a row for every location and a colum for every items stock

select * from (
select loc_id, loc_name, sto_quantity, item_name
from location, stock, item
where sto_loc_id_fk = loc_id
and sto_item_id_fk = item_id
)
pivot( sum(sto_quantity) as quantity for item_name 
    in ('Tire A' as Tire_A, 'Tire B' as Tire_B, 'Tire C' as Tire_C,
        'Screw A' as Screw_A, 'Screw B' as Screw_B, 'Screw C' as Screw_C)
);

Using this as a source for another Interactive Report gives us the required pivot view

Easy as that. If you want to try yourself, here is the SQL code to create the tables and some test data.

drop table location;
drop table stock;
drop table item;

create table location (
    loc_id                         number generated by default on null as identity 
                                   constraint location_loc_id_pk primary key,
    loc_name                       varchar2(30 char)
)
;

create table stock (
    sto_id                         number generated by default on null as identity 
                                   constraint stock_sto_id_pk primary key,
    sto_loc_id_fk                number,
    sto_item_id_fk                     number,
    sto_quantity                   number
)
;

create table item (
    item_id                        number generated by default on null as identity 
                                   constraint item_item_id_pk primary key,
    item_name                      varchar2(30 char)
)
;


create or replace procedure create_stock_data as 
    type vc_list is table of varchar2(32) 
        index by pls_integer;
    loc_list    vc_list;
    item_list   vc_list;
begin

    loc_list := vc_list('Frankfurt','Hannover','Berlin','Regensburg','Elmshorn','Bensheim');
    
    for idx in indices of loc_list loop
        dbms_output.put_line( idx || ': ' || loc_list(idx) );
        insert  into location
                (loc_id, loc_name)
        values  (idx, loc_list(idx) );
    end loop;
    
    item_list := vc_list('Tire A', 'Tire B', 'Tire C','Screw A','Screw B','Screw C');  
    
    for idx in indices of loc_list loop
        dbms_output.put_line( idx || ': ' || item_list(idx) );
        insert  into item
                (item_id, item_name)
        values  (idx, item_list(idx) );
    end loop;   

    for i in 1..100 loop
      dbms_output.put_line(i);
      insert   into stock
               (sto_id, sto_loc_id_fk, sto_item_id_fk, sto_quantity)
      values   (i, round(dbms_random.value(1,6)), round(dbms_random.value(1,6)), round(dbms_random.value(1,100)) );
    end loop;

end create_stock_data;
/