Блог

Я застрял на конкретной проблеме, я надеюсь, что кто-нибудь сможет мне помочь. У меня есть четыре таблицы 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 
  

Желаемый вид проданных продуктов суммируется и группируется по типу продукта каждый день * Пожалуйста, имейте в виду, что это всего лишь образец, поэтому я не могу группировать по идентификатору типа proudct (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
  

Вот как я бы это сделал:

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