具有动态列列表的 SQL 数据透视数据

Question

我陷入了一个特定的问题，希望有人能帮助我解决。我有四个表 Product、ProductType、Orders。我想查看“订单”表中的数据，但希望以一种方式查看数据，即每天的数据按每个产品类型下每天销售的产品数量总和进行分组。请查看表结构，我尝试将产品组作为数组列表，并且能够将每个产品类型组的产品数量相加，但无法弄清楚如何以每个产品类型组显示为的方式查看此信息每个日期的总和作为单独的列，我被告知数据透视表或交叉表查询可以做到这一点，我能够在 SQL 中创建一个逗号分隔的数组，但为了在产品类型列表中查找数据，我必须创建一个临时表并使用此处描述的函数 http://databases .aspfaq.com/database/how-do-i-simulate-an-array-inside-a-stored-procedure.html 查找匹配值...但不知道如何获得我想要的视图，我有使用数组或某些动态列表，因为我不知道可能有多少个产品类型组列？

非常感谢您的帮助，提前致谢！

表订单

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

表 产品

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

表 产品类型

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

表 商店位置

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

所需视图 每天按产品类型汇总和分组的已售产品* 请记住，这只是一个示例，因此我无法在我的产品中按产品类型 ID (1,2,3) 进行分组真正的问题可能有数百个产品类型组，因此必须动态生成列列表

   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

Answer 1

我将这样做：

/*
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)

这是使用 case 语句的扩展：

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)