drop table if exists #ItemMasterFile
drop table if exists #Bigbalprd
drop table if exists #DispatchBM
drop table if exists #DispatchDB
drop table if exists #t

Create table #ItemMasterFile (item_ID int,item_Name varchar(50))
Create table #Bigbalprd (B_ID int,item_ID int,B_QTY int,B_Weight int,B_Date date)
Create table #DispatchBM (D_ID int,Name varchar(50),D_Date date)
Create table #DispatchDB (ID int ,D_ID int,item_ID int,D_QTY int,D_Weight int)

INSERT INTO #ItemMasterFile VALUES
  (1,'A')
, (2,'B')
, (3,'C')
, (4,'D')
, (5,'e')
, (6,'f')
, (7,'g')
, (8,'h')
, (9,'K')
, (10,'L')
, (11,'M');


INSERT INTO #Bigbalprd VALUES
(111,1,1,500,'03-06-2020')
,(112,2,1,200,'03-06-2020')
,(113,1,1,300,'03-06-2020')
,(114,6,1,100,'04-06-2020')
,(115,1,1,200,'04-06-2020')
,(116,1,1,300,'04-06-2020')
,(117,7,1,100,'05-06-2020')
,(118,5,1,200,'05-06-2020')
,(119,8,1,300,'06-06-2020')

Insert into #DispatchBM Values
(1001,'Akhter','03-06-2020')
,(1002,'Irfan','05-06-2020')
Insert into #DispatchDB Values
(11,1001,1,1,500)
,(12,1001,2,1,200)
,(13,1001,1,1,300)
,(14,1002,7,1,100)
,(15,1002,5,1,200)

DECLARE @StartDate  date = '03-06-2020';
DECLARE @enddate date = '05-06-2020';

;with cte as (
select a.item_ID,upper(a.item_Name) item_Name,B_Date,sum(B_QTY) B_QTY,sum(B_Weight) B_Weight
from #ItemMasterFile a
left join #Bigbalprd b 
on a.item_ID=b.item_ID
where convert(date,B_Date,105) between @startdate and @enddate
group by a.item_ID,a.item_Name,B_Date
)
,cte1 as (
select a.item_ID,upper(a.item_Name) item_Name,  D_Date,sum(D_QTY) D_QTY,sum(D_Weight) D_Weight
from #ItemMasterFile a
left join #DispatchDB c
on c.item_ID=a.item_ID
left join #DispatchBM d
on d.D_ID=c.D_ID 
where convert(date,D_Date,105) between @startdate and @enddate
group by a.item_ID,a.item_Name,d.D_Date
)

select c.item_ID,upper(c.item_Name) item_Name,B_Date,isnull(B_QTY,0) B_QTY,isnull(B_Weight,0) B_Weight,isnull(D_QTY,0) D_QTY,isnull(D_Weight,0) D_Weight
into #t
from #ItemMasterFile c 
left join cte a on a.item_ID=c.item_ID
left join cte1 b on a.item_ID=b.item_ID
and a.B_Date=b.D_Date

DECLARE @cols NVARCHAR (MAX)

SET @cols = (SELECT DISTINCT ',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY]' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight]'
+',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY]' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight]'
                from #t where ISNULL(B_date,'')<>'' for xml path(''))

set @cols=SUBSTRING(@cols,2,len(@cols)-1)

DECLARE @cols1 NVARCHAR (MAX)

SET @cols1 = (SELECT DISTINCT ',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY]'
+',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight]'
+',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY]'
+',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight]'
                from #t where ISNULL(B_date,'')<>'' for xml path(''))

set @cols1=SUBSTRING(@cols1,2,len(@cols1)-1)

DECLARE @cols2 NVARCHAR (MAX)

SET @cols2 = (SELECT DISTINCT ',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY] NVARCHAR(1000)' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight] NVARCHAR(1000)'
+',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY] NVARCHAR(1000)' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight] NVARCHAR(1000)'
                from #t where ISNULL(B_date,'')<>'' for xml path(''))

DECLARE @cols3 NVARCHAR (MAX)

SET @cols3 = (SELECT DISTINCT ',''B_Qty'' [' + CONVERT(NVARCHAR, B_date, 23)  +'],''B_Weight'' [' + CONVERT(NVARCHAR, B_date, 23) + ']'
+',''D_QTY'' [' + CONVERT(NVARCHAR, B_date, 23) + '] ' +',''D_Weight'' [' + CONVERT(NVARCHAR, B_date, 23) + '] '
                from #t where ISNULL(B_date,'')<>'' for xml path(''))

set @cols3=SUBSTRING(@cols3,2,len(@cols3)-1)

DECLARE @query NVARCHAR(MAX)
SET @query = '  

select '''' item_id,'''' item_Name,'+@cols3+'
UNION ALL
SELECT cast(item_id as varchar(10)) item_id,item_Name,' + @cols1 + '
            FROM (
    SELECT item_ID,item_Name,  CAST(B_Date AS VARCHAR) + '' ''+ITEM AS Name, VALUE
    FROM (
        select * from #t
    )s
    UNPIVOT
    (VALUE FOR Item IN ([B_QTY], [B_Weight], [D_QTY],[D_Weight])) p
) src
             PIVOT 
             (
			MAX(VALUE) FOR Name IN (' + @cols + ')
) pvt
            '   
EXEC SP_EXECUTESQL @query