Otdfbt

sql server i need

query of

declare @fromdate varchar(100)='2019-03-01',
@Todate varchar(100)='2019-05-05',
@CompanyID varchar(100)=1,
@BookType varchar(100)=1


DECLARE @Cols NVARCHAR(max)
DECLARE @NAME nVARCHAR(1000)=''
SET @Cols=''

--make column list for PIVOT
SELECT @Cols=@Cols+ '['+s.Branch_Name +']'+ ', ' FROM
(Select distinct isnull(BH.Branch_Name,'')as Branch_Name,isnull(BH.Branch_Name+' Amount','')as amount from tbl_Branch BH
left outer join tbl_StockPosting SP
 on SP.StockPosting_BranchId =BH.Branch_ID
 left outer join [dbo].[tbl_Batch] on [Batch_Id]=SP.StockPosting_BatchID

) AS s 
declare @Cols1 nvarchar(max)
set @Cols1=''
SELECT @Cols1=@Cols1+ '['+s1.amount +']'+ ', ' FROM
(Select distinct isnull(BH.Branch_Name+' Amount','')as Amount from tbl_Branch BH
left outer join tbl_StockPosting SP
 on SP.StockPosting_BranchId =BH.Branch_ID
 left outer join [dbo].[tbl_Batch] on [Batch_Id]=SP.StockPosting_BatchID

) AS s1 
 IF @Cols<>''
BEGIN
--remove last comma from column list
SET @Cols=LEFT(@Cols,LEN(@Cols)-1)
set @Cols1=left(@Cols1,len(@Cols1)-1)
--create pivot query as we have just added distinct year list in @Cols variable
SET @Cols='SELECT * from (
Select 
sum(StockPosting_Qty) as StockPosting_Qty ,Batch_Id,Batch_No,convert(varchar,Batch_PackedDate,105)as PackedDate
,convert(varchar,Batch_ExpiryDate,105)as ExpiryDate,IT.Item_Name,ID.Item_ConversionUnit,Branch_Name,0 amount,Batch_SellingPrice from tbl_Batch
left outer join tbl_StockPosting SP on Batch_Id=StockPosting_BatchID 
inner join tbl_Item IT on IT.Item_Id=Batch_ItemId
inner join tbl_ItemDetail ID on IT.Item_Id=ID.ItemDt_ItemId
left outer join tbl_Branch BH on BH.Branch_ID=SP.StockPosting_BranchId 
where StockPosting_Date between '''+@fromdate+''' and '''+@Todate+''' 
and StockPosting_CompanyId='''+@CompanyID+'''
and StockPosting_BookType=case when '''+@BookType+'''<>0 then '''+@BookType+''' else StockPosting_BookType end
Group by Batch_Id,Batch_No,Batch_SellingPrice,Branch_Name,Batch_PackedDate,Item_Name,Batch_ExpiryDate,Item_ConversionUnit


) up
PIVOT (Sum(StockPosting_Qty) for Branch_Name in ('+@cols+','+@Cols1+')) AS pivo 
--PIVOT (Sum(StockPosting_Qty) FOR amount IN ('+@Cols1+')) AS P2
order by Batch_Id asc' 

print @Cols
print @Cols1
EXECUTE sp_executeSQL @Cols 
End

output of

 SELECT * from (
Select 
sum(StockPosting_Qty) as StockPosting_Qty ,Batch_Id,Batch_No,convert(varchar,Batch_PackedDate,105)as PackedDate
,convert(varchar,Batch_ExpiryDate,105)as ExpiryDate,IT.Item_Name,ID.Item_ConversionUnit,Branch_Name,0 amount,Batch_SellingPrice from tbl_Batch
left outer join tbl_StockPosting SP on Batch_Id=StockPosting_BatchID 
inner join tbl_Item IT on IT.Item_Id=Batch_ItemId
inner join tbl_ItemDetail ID on IT.Item_Id=ID.ItemDt_ItemId
left outer join tbl_Branch BH on BH.Branch_ID=SP.StockPosting_BranchId 
where StockPosting_Date between '2019-03-01' and '2019-05-05' 
and StockPosting_CompanyId='1'
and StockPosting_BookType=case when '1'<>0 then '1' else StockPosting_BookType end
Group by Batch_Id,Batch_No,Batch_SellingPrice,Branch_Name,Batch_PackedDate,Item_Name,Batch_ExpiryDate,Item_ConversionUnit


) up
PIVOT (Sum(StockPosting_Qty) for Branch_Name in ([KALYAN], [MUMBAI], [NERUL], [RETAILSOFT], [RETAILSOFT MUMBAI], [RETAILSOFT TECHNOLOGIES], [STORE], [THAEN], [THANE], [TURBHE], [VASHI], [WAREHOUSE],[KALYAN Amount], [MUMBAI Amount], [NERUL Amount], [RETAILSOFT Amount], [RETAILSOFT MUMBAI Amount], [RETAILSOFT TECHNOLOGIES Amount], [STORE Amount], [THAEN Amount], [THANE Amount], [TURBHE Amount], [VASHI Amount], [WAREHOUSE Amount])) AS pivo 
--PIVOT (Sum(StockPosting_Qty) FOR amount IN ([KALYAN Amount], [MUMBAI Amount], [NERUL Amount], [RETAILSOFT Amount], [RETAILSOFT MUMBAI Amount], [RETAILSOFT TECHNOLOGIES Amount], [STORE Amount], [THAEN Amount], [THANE Amount], [TURBHE Amount], [VASHI Amount], [WAREHOUSE Amount])) AS P2
order by Batch_Id asc
[KALYAN Amount], [MUMBAI Amount], [NERUL Amount], [RETAILSOFT Amount], [RETAILSOFT MUMBAI Amount], [RETAILSOFT TECHNOLOGIES Amount], [STORE Amount], [THAEN Amount], [THANE Amount], [TURBHE Amount], [VASHI Amount], [WAREHOUSE Amount]

and need result as

PIVOT (Sum(StockPosting_Qty) for Branch_Name in ([KALYAN],,[KALYAN Amount], [MUMBAI],[MUMBAI Amount], [NERUL],[NERUL Amount], [RETAILSOFT], [RETAILSOFT Amount],[RETAILSOFT MUMBAI], [RETAILSOFT TECHNOLOGIES], [RETAILSOFT MUMBAI Amount], [STORE], [STORE Amount], [THAEN],[THAEN Amount], [THANE], [THANE Amount], [TURBHE],[TURBHE Amount], [VASHI],[VASHI Amount], [WAREHOUSE], [WAREHOUSE Amount])) AS pivo

please help me