Hi akhter,
You need to make use of Group By in the Pivot query.
Refer below sample query.
SQL
CREATE TABLE #tbl1(ItemCode INT, Name VARCHAR(20))
INSERT INTO #tbl1 VALUES(1,'A')
INSERT INTO #tbl1 VALUES(2,'B')
INSERT INTO #tbl1 VALUES(3,'C')
INSERT INTO #tbl1 VALUES(4,'D')
CREATE TABLE #tbl2 (ItemCode INT, Date DATETIME,Itemqty INT)
INSERT INTO #tbl2 VALUES(1,'2019-05-01',10)
INSERT INTO #tbl2 VALUES(1,'2019-05-01',20)
INSERT INTO #tbl2 VALUES(1,'2019-05-01',30)
INSERT INTO #tbl2 VALUES(1,'2019-05-01',40)
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(Date) from #tbl2 as t1
inner join #tbl1 as t2 on t1.ItemCode=t2.ItemCode
--WHERE Date BETWEEN '2019-05-01' AND '2019-05-03'
GROUP BY Date ORDER BY Date FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query = ';WITH CTE AS(
select tda.Itemqty,tda.Date,dts.Name, dts.ItemCode
from #tbl1 as dts
inner join #tbl2 tda on tda.ItemCode = dts.ItemCode
WHERE Date BETWEEN ''2019-05-01'' AND ''2019-05-03''
)
SELECT SUM(Itemqty) Itemqty,Name,' + @cols + ' from CTE
pivot(max(ItemCode) for Date in (' + @cols + ')) p
GROUP BY Name,' + @cols + '
'
execute(@query);
DROP TABLE #tbl1
DROP TABLE #tbl2
Output
Itemqty Name May 1 2019 12:00AM
100 A 1
