Question: I frequently would like to summarize the results of my query in
heiarchical layout also
known as a Pivot table.
Here is an example of what I would like output. NULL will be printed as a space
when output.
sum of broken| source of | qty |reseller of |qty |customer with |qty
by
bolts |broken bolts|regional |broken bolts|reseller |broken bolts
|customer
-----------------------------------------------------------------------------
100 | NULL |NULL |NULL | NULL |NULL |NULL
(100/NULL) | US | 75 |NULL | NULL |NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |ACME | 35 |NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |(ACME/NULL) |(35/NULL)|Barney Rubble |20
(100/NULL) | US | 75 |NULL | NULL |NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |ACME | 35 |NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |(ACME/NULL) |(35/NULL)|Betty Rubble |5
(100/NULL) | US | 75 |NULL | NULL |NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |ABLE | 25 |NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |(ABLE/NULL) |(25/NULL)|Sponge Bobble |20
(100/NULL) | MEXICO | 15 |NULL | NULL |NULL |NULL
(100/NULL) | (MEX/NULL) |(15/NULL) |TIPPY | 12 |NULL |NULL
(100/NULL) | (MEX/NULL) |(15/NULL) |(ACME/NULL) |(12/NULL)|Juan Pepe |7
100 * * * * * *
* US 75 * * * *
* * * ACME 35 * *
* * * * * Barney Ruble 20
Where * represents NULL or a Primary Key.
How does one build a pivot table?
from tables such as:
factory_parts table
::{
part no,
plant,
qty_manufactured
plant name
}
reseller_parts table
::{
part no
plant
qty received
cost
reseller name
reseller id
}
customer_parts table
::{
reseller id
part no
plant
qty sold
qty recvd
customer id
customer name
}
Ooops now the light bulb comes on
I would do:
select factory_parts.plant name,
reseller_parts.reseller_name
customer_parts.customer_name,
customer_parts.qty_recvd
from factory_parts,reseller_parts,customer_parts
where customer_parts.part_no == 'broken_bolt' AND
( customer_parts.part_no == reseller_parts.part.no AND
customer_parts.part_no == factory_parts.part.no )
Now the question becomes how does one construct the aggregate columns
representing the sum of bolts produced by the company,made at the plant,
shipped to the reseller and sold to the customer,
then join those aggregate columns? Any suggestions?
Thank you,
Raymond
