Hi @akhter hussain ,
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)
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.