Value if null should be displayed as 0

Ramco Ramco

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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh4y ago
Test by modifying your insert as
insert into #temp
select Invoice,
sum(IsNull(T0.Qty,0)) as '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
Amit Mohanty4y ago
Hey try this.
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,IsNull(Quantity,0) Quantity,itemname from #temp) x pivot
(Sum(quantity) for itemname in (' + @cols + ')) piv ';
execute (@query)
Ramco Ramco4y ago
Hi Jignesh

Error - Isnull is not a recognized aggregate function.

Thanks
Jignesh Kumar4y ago
1. declare @cols as nvarchar(max)='';
2. declare @query as nvarchar(max)='';
3. select @cols = @cols + QUOTENAME(ItemName) + ',' from (Select distinct ItemName from #temp) as tmp
4. select @cols = substring(@cols,0,len(@cols))
5. set @query = 'select Invoice, ' + @cols + ' from (select Invoice,IsNull(Quantity,0) as Quantity,itemname from #temp) x pivot
6. ( IsNull(Sum(quantity),0) for itemname in (' + @cols + ')) piv ';
7. execute (@query)
Ramco Ramco4y ago
Hi Jignesh

still showing Null

Thanks
Jignesh Kumar4y ago
Hi Ramco,

Please use below one,
CREATE TABLE #temp(Invoice int,
Quantity numeric(19,6),itemName nvarchar(50))

insert into #temp
select Invoice,
IsNull(sum(IsNull(T0.Qty,0)),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)