Databases
StackOverflow2013
Postsdbo
POSQL pivot data with dynamic list of columns
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL pivot data with dynamic list of columns
    text
    copied!<p>Im stuck on a particular problem im hoping someone can help me out with. I have four tables Product, ProductType, Orders. I want to view Data from the Orders table but want to view it in a way where on each day the data is grouped by the sum of product quantity sold for each day under each ProductType. Please view the table structure, I have tried to get the Product groups as an array list and am able to sum the product quantity up for each product type group but cant figure out how to view this in a way where each producttype group is displayed as a sum for each date as a seperate column, I was told Pivot or a cross tab query could do this, I was able to make a comma delimited array in SQL but in order to find data in the product Type list I had to create a temp table and use a function described here <a href="http://databases.aspfaq.com/database/how-do-i-simulate-an-array-inside-a-stored-procedure.html" rel="nofollow">http://databases.aspfaq.com/database/how-do-i-simulate-an-array-inside-a-stored-procedure.html</a> to find matching values...but dont know how to get the view I want, I have to use an array or some dynamic list because I dont know how many product type group columns there could be? </p> <p>Your help would be greatly appreciated, thanks in advance!!</p> <p>Table Order</p> <pre><code>Order ID ProductID Quantity Date CustomerID Store_locID 1 1 5 10/01/2011 12 1 2 2 10 10/03/2011 4 1 3 3 8 10/03/2011 5 1 4 4 11 10/05/2011 4 2 5 5 5 10/05/2011 14 2 6 6 8 10/06/2011 3 3 </code></pre> <p>Table Products</p> <pre><code>ProductID Name Desc ProdTypeID 1 Bananas Chiquita 1 2 Apples Green Apples 1 3 Grapes Green Grapes 1 4 Potatoes Idaho potatoes 2 5 Brocolli Green Vrocolli 2 6 Plates Paper Plates 3 </code></pre> <p>Table Product Type</p> <pre><code> TypeID Name Desc 1 Fruits Fresh Fruits 2 Vegetables Fresh Veggies 3 Kitchen Kitchen stuff </code></pre> <p>Table Store Locations</p> <pre><code>loc_id city state 1 Atlanta GA 2 New york NY 3 Chicago IL </code></pre> <p>Desired View Products sold summed and grouped by Product Type on each day* Please keep in mind this is just a sample so I cant group by proudct type ID (1,2,3) in my real problem there could be hundreds of product Type groups so the list of columns has to be generated dynamically</p> <pre><code> City Date Fruits (sum) Vegetables(sum) Kitchen(sum) Group4* Group5* Atlanta 10/01/2011 5 0 0 0 Atlanta 10/03/2011 18 0 0 0 New York 10/05/2011 0 16 0 0 Chicago 10/06/2011 0 0 8 0 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload