Sparate Column Data Floor wise?

Question

I want to separate data of a column in floor wise from table #Bigbalprd FID column,mean i have two floor (First and second),so i want to separate their quantity and Weight ,sum ,if same item exit ,its qty and weight .
In Image ,F1_Qty and F1_Weight is belong to first floor and F2_Qty and F2_Weight is belong to Second floor,
Last column Total_Qty=(F1_Qty+F2_Qty) and Total_Weight=(F1_Weight+F2_Weight),also want to display all item weight their qty exit or not.
I am retrieving Data between Date.

Create table #Category(CID int,CName varchar(50))  
Create table #Section (SecID int,SecName varchar(50))  
  Create table #ItemMasterFile (Codeitem int,Descriptionitem varchar(50),Packsize varchar(50),CID int,SecId int)  
    Create table #Bigbalprd (B_ID int,Codeitem int,Bpqty int,Bweight int,Entrydate date,CID int,Secid int,FID int)  
       
  Create table #Floor (FID int,Fname Varchar(50))  
  INSERT INTO #Floor VALUES  
  (1,'First')   
  , (2,'Second');  
      
    INSERT INTO #Category VALUES  
     (11,'C1')  
    , (22,'C2')  
    , (33,'C3')  
    , (44,'C4')  
    , (55,'C5');  
  INSERT INTO #Section VALUES  
     (1,'S1')  
    , (2,'S2')  
    , (1,'S3')  
    , (2,'S4')  
    , (1,'S5');  
          
 INSERT INTO #ItemMasterFile VALUES  
      (1,'A','Bigbale',11,1)  
    , (2,'B','Bigbale',22,2)  
    , (3,'C','Bigbale',33,1)  
    , (4,'D','Bigbale',33,1)  
    , (5,'e','Bigbale',44,1)  
    , (6,'f','Bigbale',55,4)  
    , (7,'g','Bigbale',11,4)  
    , (8,'h','Bigbale',55,2)  
    , (9,'K','Bigbale',55,3)  
    , (10,'L','Bigbale',44,3)  
    , (11,'M','Bigbale',22,3);  
                  
                  
    INSERT INTO #Bigbalprd VALUES  
    (111,1,1,500,'04-01-2021',11,1,1)  
    ,(112,2,1,200,'04-01-2021',22,2,2)  
    ,(113,1,1,300,'04-01-2021',11,1,1)  
    ,(114,6,1,100,'04-01-2021',55,4,1)  
    ,(115,1,1,200,'04-01-2021',11,1,1)  
    ,(116,1,1,300,'04-01-2021',11,1,1)  
    ,(117,7,1,100,'04-01-2021',11,4,2)  
    ,(118,5,1,200,'04-01-2021',44,1,2)  
    ,(119,8,1,300,'04-01-2021',55,3,1)  
     ,(120,2,1,300,'04-01-2021',22,2,2)  
 , (121,1,1,800,'04-01-2021',11,1,2)  
 , (122,1,1,400,'04-01-2021',11,1,2)  
  
Expected Output  

Answer 1

Hi @Analyst_SQL ,

After checking, the insert data of #Section should be as below:

   INSERT INTO #Section VALUES  
      (1,'S1')  
     , (2,'S2')  
     , (3,'S3')  
     , (4,'S4')  
     , (5,'S5');  

Please refer below:

;with cte as (  
select a.Codeitem,a.Descriptionitem,b.CName,c.SecName,sum(d.Bpqty) Bpqty,sum(d.Bweight) Bweight  
,'F'+cast(d.FID as char(1))+'_QTY'  FID_QTY  
,'F'+cast(d.FID as char(1))+'_Weight'  FID_Weight  
from #ItemMasterFile a  
left join #Category b on a.CID=b.CID  
left join #Section c on a.SecId=c.SecID  
left join #Bigbalprd d on a.Codeitem=d.Codeitem  
group by a.Codeitem,a.Descriptionitem,b.CName,c.SecName,d.FID )  
,cte1 as (  
select Codeitem,upper(Descriptionitem) Descriptionitem,CName,SecName  
,isnull(max([F1_QTY]),0) [F1_QTY]  
,isnull(max([F1_Weight]),0) [F1_Weight]  
,isnull(max([F2_QTY]),0)  [F2_QTY]  
,isnull(max([F2_Weight]),0)  [F2_Weight]  
FROM cte AS R  
    PIVOT(MAX(Bpqty) FOR FID_QTY IN ([F1_QTY], [F2_QTY])) AS QTY  
    PIVOT(MAX(Bweight) FOR FID_Weight IN ([F1_Weight], [F2_Weight])) AS Weight  
group by Codeitem,Descriptionitem,CName,SecName)  
,cte2 as (  
select *,[F1_QTY]+[F2_QTY] Total_Qty,[F1_Weight]+[F2_Weight] Total_Weight  
 from cte1)  
 select * from (  
 select * from cte2  
 union  
 select NULL,NULL,NULL,NULL,sum([F1_QTY]),sum([F1_Weight]),sum([F2_QTY]),sum([F2_Weight]),sum(Total_Qty),sum(Total_Weight)  
 from cte2 ) a  
 order by IIF(Codeitem is null,99,Codeitem)  

Output:

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.