具有动态列列表的 SQL 数据透视数据 [英] SQL pivot data with dynamic list of columns

问题描述

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

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!!

表顺序

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 

Table Store 位置

Table Store Locations

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

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

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

推荐答案

我会这样做:

/*
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 语句的扩展:

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)

这篇关于具有动态列列表的 SQL 数据透视数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！