How to Replace (Null) Values with 0 Output in Pivot

How to replace (null) values with 0 output in PIVOT

SELECT CLASS,
isnull([AZ],0),
isnull([CA],0),
isnull([TX],0)
FROM #TEMP
PIVOT (SUM(DATA)
FOR STATE IN ([AZ], [CA], [TX])) AS PVT
ORDER BY CLASS

How to replace NULL with zero in the output of this pivot query?

It's SUM(x.[Amount]) that has the value NULL here, not [mth]. I'm not sure you can, however, use an ISNULL in the PIVOT (no Sample data with which to actually check (and I tend to use do a cross pivot, rather than using PIVOT). If you can't you'll need to put the ISNULL in your outer SELECT statemeent:

SELECT [Year], Market,
       ISNULL(Jan,0) AS Jan,
       ISNULL(Feb,0) AS Feb, ...

how to Replace null with zero in pivot SQL query

create table tblInvoice (InvDt date, TenantId int, InvAmnt numeric)
insert into tblInvoice values ('20180601',1,1),('20180601',1,1),('20180601',1,1),('20180501',1,1), ('20180401',1,1)

SELECT Year
     , COALESCE(Jan   ,0) Jan
     , COALESCE(Feb   ,0) Feb
     , COALESCE(Mar   ,0) Mar
     , COALESCE(Apr   ,0) Apr
     , COALESCE(May   ,0) May
     , COALESCE(Jun   ,0) Jun
     , COALESCE(Jul   ,0) Jul
     , COALESCE(Aug   ,0) Aug
     , COALESCE(Sep   ,0) Sep
     , COALESCE(Oct   ,0) Oct
     , COALESCE(Nov   ,0) Nov
     , COALESCE(Dec   ,0) Dec
  FROM (   SELECT YEAR(InvDt)                     AS Year
                , LEFT(DATENAME(MONTH, InvDt), 3) AS Month
                , InvAmnt                         AS Amount
             FROM tblInvoice
            WHERE TenantId = 1) AS Inv
  PIVOT (   SUM(Amount)
            FOR Month IN (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)) AS pvt;

How to replace the NULL values in a pivot output with zeroes?

You can use COALESCE to achieve the result.

SQL Fiddle

CREATE TABLE [dbo].[Matrix]
(
        [A]     INT
    ,   [B]     INT
    ,   [VAL]   FLOAT
);

INSERT INTO [dbo].[Matrix] ([A], [B], [VAL]) 
    VALUES
        (1, 1, -218.46)
    ,   (1, 2, -2.1846000000000e+02)
    ,   (2, 1, 6.4000000000000e+01)
    ,   (2, 2, -218.46)
    ,   (2, 3, 6.4000000000000e+01)
    ,   (3, 2, -2.1846000000000e+02)
    ,   (3, 3, -218.46)
    ,   (4, 3, 6.4000000000000e+01)
    ,   (4, 4, -218.46)
    ,   (4, 5, 6.4000000000000e+01)
    ,   (5, 4, 6.4000000000000e+01)
    ,   (5, 5, -218.46)
    ,   (5, 6, 6.4000000000000e+01)
    ,   (6, 5, 6.4000000000000e+01)
    ,   (6, 6, -218.46)
    ,   (6, 7, 6.4000000000000e+01)
    ,   (7, 6, 6.4000000000000e+01)
    ,   (7, 7, -218.46)
    ,   (7, 8, 6.4000000000000e+01)
    ,   (8, 7, 6.4000000000000e+01)
    ,   (8, 8, -218.46)
    ,   (8, 9, 6.4000000000000e+01)
    ,   (9, 8, 6.4000000000000e+01)
    ,   (9, 9, -218.46)
    ,   (9, 10, 6.4000000000000e+01)
    ,   (10, 9, 6.4000000000000e+01)
    ,   (10, 10, -218.46);

    SELECT  COALESCE([1], 0)    [1]
        ,   COALESCE([2], 0)    [2]
        ,   COALESCE([3], 0)    [3]
        ,   COALESCE([4], 0)    [4]
        ,   COALESCE([5], 0)    [5]
        ,   COALESCE([6], 0)    [6]
        ,   COALESCE([7], 0)    [7]
        ,   COALESCE([8], 0)    [8]
        ,   COALESCE([9], 0)    [9]
        ,   COALESCE([10], 0)   [10]
    FROM
        (   
            SELECT  x = a
                ,   y = b
                ,   v = val
            FROM    [dbo].[Matrix]
        ) AS dataToPivot
        PIVOT
        (
            MIN(v)
            FOR y IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])
        ) AS pivoted
    ORDER BY x;

Replace NULL values to 0 via SQL Pivot

Rather than top SELECT * you can specify the columns/values via IsNull()

Example

SELECT Name
      ,[January]   = isnull([January],0)
      ,[February]  = isnull([February],0)
      ,[March]     = isnull([March],0)
      ,[April]     = isnull([April],0)
      ,[May]       = isnull([May],0)
      ,[June]      = isnull([June],0)
      ,[July]      = isnull([July],0)
      ,[August]    = isnull([August],0)
      ,[September] = isnull([September],0)
      ,[October]   = isnull([October],0)
      ,[November]  = isnull([November],0)
      ,[December]  = isnull([December],0)
      ....

Replace NULL with Zeros MS ACCESS PIVOT

Your underlying recordset is not updateable and your Null handling code is attempting to actually change the values of the fields in the recordset. As @Nathan_Sav suggests, use the Nz() function to return 0 when you encounter Nulls.

---

Related Topics