PIVOT function returning NULL values and ISNULL not removing

I have a query using PIVOT function that has NULL values that wish to replace with 0 in the cell. I have tried to add the ISNULL into the sub query against O7CRAM but that did not remove the NULL values

    select
        * 
    from 
    (
      select 
      O7ORNO,
      O7CRID,
      O7CRAM
    
      from MVXJDTA.OOLICH 
    ) src
    pivot
    (
      sum(O7CRAM)
      for O7CRID in ([PNP], [DTY], [HAN])
    ) piv
     order by O7ORNO;

The issue I have now is the ISNULL and where to place it to remove the NULL values.

Current Output:

O7ORNO	PNP	DTY	HAN
100000329	0.85	NULL	NULL

Desired Output:

O7ORNO	PNP	DTY	HAN
100000329	0.85	0.00	0.00

Answer

Here’s a way to do the same thing with the ancient, arcane, but simple “Black Arts” method known as a CROSSTAB. Notice how you do NOT neet explicit ISNULL()s.

--===== Same as the PIVOT
 SELECT  O7ORNO
        ,PNP = SUM(IIF(O7CRID = 'PNP',O7CRAM,0)
        ,DTY = SUM(IIF(O7CRID = 'DTY',O7CRAM,0)
        ,HAN = SUM(IIF(O7CRID = 'HAN',O7CRAM,0)
   FROM MVXJDTA.OOLICH
  GROUP BY O7ORNO
  ORDER BY O7ORNO
;

Here’s a slightly different take with row/column totals and a grand total.

--===== Same as the PIVOT with row/column totals and grand total
 SELECT  O7ORNO
        ,PNP = SUM(IIF(O7CRID = 'PNP',O7CRAM,0)
        ,DTY = SUM(IIF(O7CRID = 'DTY',O7CRAM,0)
        ,HAN = SUM(IIF(O7CRID = 'HAN',O7CRAM,0)
        ,RowTotal = SUM(O7CRAM)
   FROM MVXJDTA.OOLICH 
  GROUP BY O7ORNO WITH ROLLUP
  ORDER BY GROUPING(O7ORNO),O7ORNO
;

For more information about how CROSSTABs work and for how to make Dynamic CROSSTABS, please see the articles at the following links:

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-converting-rows-to-columns-1

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs