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


