List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 8 2005 6:28pm
Subject:Re: Does such a JOIN exist that can create a pivot table?
View as plain text  
Raymond,

Can you bend one or more of the pivot table examples at 
http://www.artfulsoftware.com/queries.php to your requirement?

PB

-----

Jacob, Raymond A Jr wrote:

>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
>
>
>
>
>  
>
>------------------------------------------------------------------------
>
>No virus found in this incoming message.
>Checked by AVG Anti-Virus.
>Version: 7.0.308 / Virus Database: 266.9.5 - Release Date: 4/7/2005
>  
>

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.5 - Release Date: 4/7/2005
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