6
Answers

Value if null should be displayed as 0

Photo of Ramco Ramco

Ramco Ramco

3y
631
1

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)
SQL

Thanks

Answers (6)

3
Photo of Amit Mohanty
16 52.2k 6.1m 3y
Hey try this.
  1. declare @cols as nvarchar(max)='';  
  2. declare @query as nvarchar(max)='';  
  3. select @cols = @cols + QUOTENAME(ItemName) + ',' from (Select distinct ItemName from #tempas tmp  
  4. select @cols = substring(@cols,0,len(@cols))  
  5. set @query = 'select Invoice, ' + @cols + ' from (select Invoice,IsNull(Quantity,0) Quantity,itemname from #temp) x pivot  
  6. (Sum(quantity) for itemname in (' + @cols + ')) piv ';  
  7. execute (@query) 
2
Photo of Sachin Singh
NA 55.8k 88.5k 3y
Test by modifying your insert as
  1. insert into #temp  
  2. select Invoice,  
  3. sum(IsNull(T0.Qty,0)) as 'Quantity', T2.ItemName  
  4. From DR1 T0  
  5. inner join ORD T1 on T0.doc = T1.doc  
  6. inner join Oit T2 on T0.Item = T2.Item  
  7. where T1.DDATE = '2022/04/20'  
  8. GROUP BY T1.Invoice,T2.ItemName  
 
2
Photo of Ramco Ramco
399 3.8k 687.5k 3y
Hi Jignesh
 
   Error - Isnull is not a recognized aggregate function.
 
Thanks 
2
Photo of Jignesh Kumar
29 39.6k 2.9m 3y
  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)  
2
Photo of Ramco Ramco
399 3.8k 687.5k 3y
Hi Jignesh
 
  still showing Null
 
Thanks 
2
Photo of Jignesh Kumar
29 39.6k 2.9m 3y
Hi Ramco,
 
Please use below one,
  1. CREATE TABLE #temp(Invoice int,  
  2. Quantity numeric(19,6),itemName nvarchar(50))  
  3.   
  4. insert into #temp  
  5. select Invoice,  
  6. IsNull(sum(IsNull(T0.Qty,0)),0) 'Quantity', T2.ItemName  
  7. From DR1 T0  
  8. inner join ORD T1 on T0.doc = T1.doc  
  9. inner join Oit T2 on T0.Item = T2.Item  
  10. where T1.DDATE = '2022/04/20'  
  11. GROUP BY T1.Invoice,T2.ItemName  
  12.   
  13. declare @cols as nvarchar(max)='';  
  14. declare @query as nvarchar(max)='';  
  15. select @cols = @cols + QUOTENAME(ItemName) + ',' from (Select distinct ItemName from #tempas tmp  
  16. select @cols = substring(@cols,0,len(@cols))  
  17. set @query = 'select Invoice, ' + @cols + ' from (select Invoice,Quantity,itemname from #temp) x pivot  
  18. Sum(quantity) for itemname in (' + @cols + ')) piv ';  
  19. execute (@query)