title: SQL pivot data with dynamic list of columns
body: <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>