Get all Items sold based on a collection of Items from a master table in SQL Server

Question

I have an Item Table where all transactions are stored by different site location and business date, I also have a key for each order stored.My item table structure is as below:

Item Table

SiteID   BusinessDate   ItemName   Units Sold   Units Sale   ItemNo  OrderNo
----------------------------------------------------------------------------
1        06/08/2018     Apple         1             5        10001    122-1
1        06/08/2018     Coffee        1             16       10002    122-1
1        06/08/2018     Bread         2             7        10003    122-1
1        06/08/2018     Beans         9             18       10004    122-1

1        06/08/2018     Apple         2             5        10001    122-4
1        06/08/2018     Coffee        2             6        10002    122-4
1        06/08/2018     Bread         1             7        10003    122-4
1        06/08/2018     Beans         4             8        10004    122-4

2        06/08/2018     Apple         2             5        10001    122-2
2        06/08/2018     Coffee        1             6        10002    122-2

3        06/08/2018     Bread         3             5        10003    122-3
3        06/08/2018     Beans         7             17       10004    122-3

also, my master menu items table structure is as below:

Item Master

ItemNo      Name      Category 
---------------------------------
10001       Apple     C1    
10002       Coffee    C1  
10003       Bread     C2   
10004       Beans     C2   

Expected Results: I want to Generate the following output:

ItemNo      Name      C1      C2 
---------------------------------
10001       Apple     5       3
10002       Coffee    4       3
10003       Bread     3       6
10004       Beans     13      20

In the above output, basically we are checking each items from the master table against each category set defined within it.

The values under column C1 are the actual units sold that went together with Apple or Coffee after checking all the records order by order in my Item table.

We are taking one base item such as Apple from Master table and checking in our Item table what was sold with apple under category C1. Example: how many items of Bread was sold together with either coffee or apple?

Answer 1

This will probably get you most of the way there. I did not put in logic to move the summary columns around as that would only work for two categories, and I assume you have many more.

CREATE TABLE #Master(ItemNo int,[Name] varchar(50), Category varchar(5)) 
INSERT INTO #Master VALUES(10001,'Apple','C1' )   
INSERT INTO #Master VALUES(10002, 'Coffee' ,'C1')  
INSERT INTO #Master VALUES(10003,'Bread','C2')   
INSERT INTO #Master VALUES(10004,'Beans' ,'C2')  

CREATe TABLE #Transactions(SiteID int,BusinessDate date,ItemName varchar(50),[Units Sold]int,[Units Sale]int,ItemNo int,  OrderNo varchar(20))
INSERT INTO #Transactions VALUES(1,'06/08/2018','Apple',1,5,10001,'122-1')
INSERT INTO #Transactions VALUES(1,'06/08/2018','Coffee',1,16,10002,'122-1')
INSERT INTO #Transactions VALUES(1,'06/08/2018','Bread',2,7,10003,'122-1')
INSERT INTO #Transactions VALUES(1,'06/08/2018','Beans',9,18,10004,'122-1')
INSERT INTO #Transactions VALUES(1,'06/08/2018','Apple',2,5,10001,'122-4')
INSERT INTO #Transactions VALUES(1,'06/08/2018','Coffee',2,6,10002,'122-4')
INSERT INTO #Transactions VALUES(1,'06/08/2018','Bread',1,7,10003,'122-4')
INSERT INTO #Transactions VALUES(1,'06/08/2018','Beans',4,8,10004,'122-4')
INSERT INTO #Transactions VALUES(2,'06/08/2018','Apple',2,5,10001,'122-2')
INSERT INTO #Transactions VALUES(2,'06/08/2018','Coffee',1,6,10002,'122-2')
INSERT INTO #Transactions VALUES(3,'06/08/2018','Bread',3,5,10003,'122-3')
INSERT INTO #Transactions VALUES(3,'06/08/2018','Beans',7,17,10004,'122-3')


;WITH CTE AS (
select MS.ItemNo,MS.Name,MS.Category, SUM(TR.[Units Sold]) RootItemSold, tr.OrderNo
from #Master MS
INNER JOIN #Transactions TR on MS.ItemNo = TR.ItemNo
GROUP BY MS.ItemNo,MS.Name,MS.Category, tr.OrderNo
),
MiddleCalc as
(
select ItemNo, [Name],Category,RootItemSold,OrderNo
,(SELECT ISNULL(SUM(1),0)  FROM CTE T2 where TRoot.OrderNo = t2.OrderNo and TRoot.Category <> t2.Category) HasOtherCategory
from CTE TRoot
)
SELECT ItemNo,[Name],[Category], 
CASE WHEN Category = 'C1' THEN
        SUM(RootItemSold)  
    ELSE
        SUM(CASE WHEN HasOtherCategory <> 0 THEN RootItemSold ELSE 0 END)  END C1,
CASE WHEN Category = 'C2' THEN
        SUM(RootItemSold)  
    ELSE
        SUM(CASE WHEN HasOtherCategory <> 0 THEN RootItemSold ELSE 0 END)  END C2
FROM MiddleCalc
GROUP BY ItemNo,[Name],[Category]