--------------050106000801040408030109-- --=======AVGMAIL-4256CD641C70======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" 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 --=======AVGMAIL-4256CD641C70=======--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