SQL pivot data with dynamic list of columns

Question

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 http://databases.aspfaq.com/database/how-do-i-simulate-an-array-inside-a-stored-procedure.html 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?

Your help would be greatly appreciated, thanks in advance!!

Table Order

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

Table Products

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

Table Product Type

 TypeID      Name       Desc   
  1          Fruits     Fresh Fruits
  2          Vegetables Fresh Veggies
  3          Kitchen    Kitchen stuff

Table Store Locations

loc_id      city        state
   1        Atlanta     GA
   2        New york    NY
   3        Chicago     IL

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

   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

What do you use? MySQL or MS SQL Server?

Devart
– Devart

2011-10-25 08:16:49 +00:00
Commented Oct 25, 2011 at 8:16 — Devart
– Devart, Commented Oct 25, 2011 at 8:16
OK. I was going to help you with MySQL;-)

Devart
– Devart

2011-10-25 14:42:59 +00:00
Commented Oct 25, 2011 at 14:42 — Devart
– Devart, Commented Oct 25, 2011 at 14:42

Michal Barcik · Accepted Answer · 2011-10-25 14:43:00Z

3

Here is how I would do that:

/*
create table ##order  ( ID int, ProdID int, Quantity int, [Date] date, CustID int, StoreID int );
insert into ##order values (1,1,5,'2011-01-10' ,12,1), (2,2,10,'2011-03-10',4 ,1), (3,3,8,'2011-03-10' ,5 ,1), (4,4,11,'2011-05-10',4 ,2), (5,5,5,'2011-05-10' ,14,2), (6,6,8,'2011-06-10' ,3 ,3);
create table ##product (ProdID int, ProdName varchar(64), ProdDesc varchar(255), ProdType int);
insert into ##product values (1,'Bananas','Chiquita',1), (2,'Apples','Green Apples',1), (3,'Grapes','Green Grapes',1), (4,'Potatoes','Idaho potatoes',2), (5,'Brocolli','Green Brocolli',2), (6,'Plates','Paper Plates',3);
create table ##ProdType (TypeID int, Name varchar(64), [Desc] varchar(255));
insert into ##ProdType values (1,'Fruits','Fresh Fruits'),(2,'Vegetables','Fresh Veggies'),(3,'Kitchen','Kitchen stuff');
create table ##loc (loc_id int, city varchar(50), [state] varchar(50))
insert into ##loc values(1, 'Atlanta','GA'), (2, 'New york', 'NY'), (3, 'Chicago', 'IL');
*/

declare @cmd varchar(max), @columns varchar(max)
set @columns = ''


select @columns = @columns + '['+Name+'],' from ##ProdType order by name asc
select @columns = substring(@columns, 0, len(@columns))

set @cmd = '
select city, date,'+@columns+' from
(
select   
   ct.city,
   ord.Date,
   pt.Name,
   ord.Quantity
from 
   ##order ord 
   join ##product pr on ord.ProdID = pr.ProdID
   join ##ProdType pt on pr.ProdType = pt.TypeID
   join ##loc ct on ord.StoreID = ct.loc_id
)date_to_pivot
PIVOT
(
SUM(Quantity) for Name in ('+@columns+')
)PIVOTED_DATA'   

exec (@cmd)

Here is the extension using the case statements:

set @columns = ''

select @columns = ''
select @columns = @columns + 'sum(case when pt.Name = '''+Name+''' then ord.Quantity else 0 end) as '+Name+'_SUM,' from ##ProdType order by name asc
select @columns = @columns + 'count(case when pt.Name = '''+Name+''' then ord.Quantity else 0 end) as '+Name+'_COUNT,' from ##ProdType order by name asc
select @columns = substring(@columns, 0, len(@columns))


set @cmd = '
select   
   ct.city,
   ord.Date,   '
   +@columns+
'   
from 
   ##order ord 
   join ##product pr on ord.ProdID = pr.ProdID
   join ##ProdType pt on pr.ProdType = pt.TypeID
   join ##loc ct on ord.StoreID = ct.loc_id
group by
   ct.city,
   ord.Date'

   exec (@cmd)

edited Oct 25, 2011 at 14:43

answered Oct 25, 2011 at 7:17

Michal Barcik

6624 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Shokwave Over a year ago

Hi Michal Thanks for your reply, I got your suggestion to work for Quantity but I have another question or two :) 1) If I want to display the Sum for another column besides quantity just for example sake say SUM(Store_locID ) how could I do that I tried to do PIVOT ( SUM(Quantity) for Name in ('+@columns+'), SUM(Store_locID) for Name in ('+@columns+') )PIVOTED_DATA' but it doesnt let me add any additional columns I would like to see the sum for? 2) How could I use Alias or rename the Sum quantity columns for ex Veggies(Sum), Fruits(Sum) etc

Michal Barcik Over a year ago

You can not do that in one PIVOT operator, the limitation is that you can use only one aggregation. What you could do is execute 2 of this statements and then join them based on the date and product type. Or you could revrite the PIVOT operator using the case statement.

Michal Barcik Over a year ago

I added the case stataments, so you only need to add the desired aggregate to the @column variable

Shokwave Over a year ago

hmm ok when I do a join its complaining the columns in the top select are ambigious...Please keep in mind the ProductType Groups are common to both Items I am trying to show the Sum for lets call them ProductBoxes for example sake...they share the same product Types so the column names will be similar unless I make another @columns list and name the producttypes in that different maybe?

Collectives™ on Stack Overflow

SQL pivot data with dynamic list of columns

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related