Split data from one column into two column.

Question

I am trying to splitting one column data into two column,Means Data Floor One(F1_QTY,F1_Weight) and Floor Two(F2_QTY,F2_Weight),Currently getting result on Floor One ,but Floor Two(F2_QTY,F2_Weight) is coming null.
I also want to display all items.

Dumpy Data

Create table #Section (SecID int,SecName varchar(50))  
Create table #Categoory(CID int,CName 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,delid int,Secid int,CID int,FID int)  
  
  INSERT INTO #Section VALUES  
    (11,'S1')  
  , (22,'S2')  
  , (33,'S3')  
  , (44,'S4')  
  , (55,'S5')  
;  
  INSERT INTO #Categoory VALUES  
   (1,'C1')  
  , (2,'C2')  
  , (3,'C3')  
  , (4,'C4')  
  , (5,'C5')  
;  
  
  
          
  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-06-2020',11,1,1,null)  
  ,(112,2,1,200,'04-06-2020',22,2,2,null)  
  ,(113,1,1,300,'04-06-2020',33,11,1,null)  
  ,(114,6,1,100,'04-06-2020',55,4,1,null)  
  ,(115,1,1,200,'04-06-2020',11,1,1,null)  
  ,(116,1,1,300,'04-06-2020',11,1,1,null)  
  ,(117,7,1,100,'04-06-2020',11,4,1,null)  
  ,(118,5,1,200,'04-06-2020',44,1,2,null)  
  ,(119,8,1,300,'04-06-2020',55,2,2,null)  
          



;with   
cte2 as (SELECT Catagory.CName as Catagory ,ItemMasterFile.Descriptionitem as Artical, F.FName as F1,  
Sum(Bigbalprd.Bpqty) as F1_QTY,Sum(Bigbalprd.Bweight) as F1_Weight   
    FROM         Bigbalprd INNER JOIN   
  
                         ItemMasterFile ON Bigbalprd.CodeItem = ItemMasterFile.CodeItem  INNER JOIN   
                         Catagory ON Catagory.CID = Bigbalprd.CID  
    inner join Floor F on F.FID=Bigbalprd.FID  
                        and Bigbalprd.Entrydate between '2021-04-01' and '2021-04-01' and Bigbalprd.delID is null and F.FID=1  
   group by ItemMasterFile.Descriptionitem,Catagory.CName,F.FName)   
  
,cte3 as (SELECT Catagory.CName as Catagory ,ItemMasterFile.Descriptionitem as Artical, F.FName as F2,  
Sum(Bigbalprd.Bpqty) as F2_QTY,Sum(Bigbalprd.Bweight) as F2_Weight  
    FROM         Bigbalprd INNER JOIN   
  
                         ItemMasterFile ON Bigbalprd.CodeItem = ItemMasterFile.CodeItem  INNER JOIN   
                         Catagory ON Catagory.CID = Bigbalprd.CID  
    inner join Floor F on F.FID=Bigbalprd.FID  
                        and Bigbalprd.Entrydate between '2021-04-02' and '2021-04-02' and Bigbalprd.delID is null and F.FID=2  
   group by ItemMasterFile.Descriptionitem,Catagory.CName,F.FName)  
  
   select   cte2.Catagory as Category,cte2.Artical, F1_QTY,F1_Weight,F2_QTY,F2_Weight from cte2 left join cte3 on   
   cte3.F2=cte2.F1   

 

Answer 1

Your code returns the correct result. So what is the output you expect?
Your dummy data can only return null values, so I modified the test data:

 Create table #Categoory(CID int,CName 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,delid int,Secid int,CID int,FID int)  
   
 Create table #Floor (FID int,Fname Varchar(50))  
 INSERT INTO #Floor VALUES  
 (1,'FIrst')   
 , (2,'Second'),  
 (1,'Second')  
  
   INSERT INTO #Categoory VALUES  
    (1,'C1')  
   , (2,'C2')  
   , (1,'C3')  
   , (2,'C4')  
   , (1,'C5');  
      
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,1)  
   ,(112,2,1,200,'04-02-2021',null,2,2,2)  
   ,(113,1,1,300,'04-01-2021',33,11,1,1)  
   ,(114,6,1,100,'04-02-2021',55,4,1,2)  
   ,(115,1,1,200,'04-01-2021',null,1,1,1)  
   ,(116,1,1,300,'04-02-2021',11,1,1,1)  
   ,(117,7,1,100,'04-01-2021',11,4,1,2)  
   ,(118,5,1,200,'04-02-2021',null,1,2,2)  
   ,(119,8,1,300,'04-01-2021',null,2,2,1)  
  
select * from #Bigbalprd  
select * from #ItemMasterFile  
select * from #Categoory   
select * from #Floor  
  
              
 ;with   
 cte2 as (SELECT #Categoory.CName as Catagory ,#ItemMasterFile.Descriptionitem as Artical, F.FName as F1,  
 Sum(#Bigbalprd.Bpqty) as F1_QTY,Sum(#Bigbalprd.Bweight) as F1_Weight   
 FROM  #Bigbalprd  
 INNER JOIN #ItemMasterFile   
 ON #Bigbalprd.CodeItem = #ItemMasterFile.CodeItem    
 INNER JOIN #Categoory ON #Categoory.CID = #Bigbalprd.CID  
 inner join #Floor F   
 on F.FID=#Bigbalprd.FID and #Bigbalprd.Entrydate between '2021-04-01' and '2021-04-01' and #Bigbalprd.delID is null and F.FID=1  
group by #ItemMasterFile.Descriptionitem,#Categoory.CName,F.FName)   
      
 ,cte3 as (SELECT #Categoory.CName as Catagory ,#ItemMasterFile.Descriptionitem as Artical, F.FName as F2,  
 Sum(#Bigbalprd.Bpqty) as F2_QTY,Sum(#Bigbalprd.Bweight) as F2_Weight  
 FROM  #Bigbalprd   
 INNER JOIN #ItemMasterFile   
 ON #Bigbalprd.CodeItem = #ItemMasterFile.CodeItem    
 INNER JOIN #Categoory ON #Categoory.CID = #Bigbalprd.CID  
inner join #Floor F   
on F.FID=#Bigbalprd.FID and #Bigbalprd.Entrydate between '2021-04-02' and '2021-04-02' and #Bigbalprd.delID is null and F.FID=2  
group by #ItemMasterFile.Descriptionitem,#Categoory.CName,F.FName)  
      
select   cte2.Catagory as Category,cte2.Artical, F1_QTY,F1_Weight,F2_QTY,F2_Weight   
from cte2   
left join cte3 on cte3.F2=cte2.F1   

Output：

What matters is what kind of result you want, and what your data structure looks like.If we have this information, we can avoid endless games of riddles.

Regards
Echo