Hi
I want if value in Pivot is null then it should be displayed as 0. I also want to display Totals.
CREATE TABLE #temp(Invoice int,
Quantity numeric(19,6),itemName nvarchar(50))
insert into #temp
select Invoice,
IsNull(sum(T0.Qty),0) 'Quantity', T2.ItemName
From DR1 T0
inner join ORD T1 on T0.doc = T1.doc
inner join Oit T2 on T0.Item = T2.Item
where T1.DDATE = '2022/04/20'
GROUP BY T1.Invoice,T2.ItemName
declare @cols as nvarchar(max)='';
declare @query as nvarchar(max)='';
select @cols = @cols + QUOTENAME(ItemName) + ',' from (Select distinct ItemName from #temp) as tmp
select @cols = substring(@cols,0,len(@cols))
set @query = 'select Invoice, ' + @cols + ' from (select Invoice,Quantity,itemname from #temp) x pivot
( Sum(quantity) for itemname in (' + @cols + ')) piv ';
execute (@query)
Thanks