List:General Discussion« Previous MessageNext Message »
From:Dan Bolser Date:April 8 2005 6:21pm
Subject:Re: Does such a JOIN exist that can create a pivot table?
View as plain text  
I think what you are talking about could be called a 'crosstabulation' or
a crosstab. 

Their are some tutorials about making cross-tabs using perl. I have used
them a lot, and they are really great.

I tend to stack up lots of IF statemens...

Table1

month	person	sex	sales
1	a	m	10	
1	b	f	20
1	c	m	30
2	a	m	40
2	b	f	50
2	c	m	60


select 
  month,
  sum(if(sex='m',sales,0)) as male_sales,
  sum(if(sex='f',sales,0)) as female_sales,
  sum(sales) as total
from 
  Table1
group by
  month;

This would give (I think)...

month	male_sales	female_sales	total
1	40		20		60
2	100		50		150

You could easily add a...

count(distinct(if(sex='m',person,NULL))) as total_men,
count(distinct(if(sex='f',person,NULL))) as total_women,

to create average sales for men and women, or anything else you want.

Does that look right?


On Fri, 8 Apr 2005, 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
>
>
>
>

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