List:General Discussion« Previous MessageNext Message »
From:Raymond A Jr Jacob Date:April 8 2005 5:15pm
Subject:Does such a JOIN exist that can create a pivot table?
View as plain text  
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




Thread
Does such a JOIN exist that can create a pivot table?Raymond A Jr Jacob8 Apr
  • Re: Does such a JOIN exist that can create a pivot table?Dan Bolser8 Apr
  • Re: Does such a JOIN exist that can create a pivot table?Peter Brawley8 Apr