How to replace null values with 0 output in PIVOT

Question

I tried to convert the  null  values with  0  zeros  output in PIVOT function but have no sucess   Below is the table and the syntax i ve tried   SELECT CLASS   AZ    CA    TX  FROM  TEMP PIVOT  SUM DATA  FOR STATE IN   AZ    CA    TX    AS PVT ORDER BY CLASS  CLASS   AZ  CA      TX RICE    10  4        null  COIN    30  3        2 VEGIE    null   null  9   I tried to use the ISNULL but did not work   PIVOT SUM ISNULL DATA 0   AS QTY   Could someone please look into its syntax error  Thanks a lot

User · Accepted Answer

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

User · Answer

You cannot place the IsNull   until after the data is selected so you will place the IsNull   around the final value in the SELECT   SELECT CLASS    IsNull  AZ   0  as  AZ     IsNull  CA   0  as  CA     IsNull  TX   0  as  TX  FROM  TEMP PIVOT      SUM DATA    FOR STATE IN   AZ    CA    TX     AS PVT ORDER BY CLASS

User · Answer

If you have a situation where you are using dynamic columns in your pivot statement you could use the following   DECLARE  cols               NVARCHAR MAX  DECLARE  colsWithNoNulls    NVARCHAR MAX  DECLARE  query              NVARCHAR MAX   SET  cols   STUFF  SELECT distinct       QUOTENAME Name               FROM Hospital             WHERE Active   1 AND StateId IS NOT NULL             FOR XML PATH      TYPE               value       NVARCHAR MAX              1 1      SET  colsWithNoNulls   STUFF                                SELECT distinct   ISNULL     QUOTENAME Name         No        QUOTENAME Name                  FROM Hospital                 WHERE Active   1 AND StateId IS NOT NULL                 FOR XML PATH      TYPE               value       NVARCHAR MAX              1 1      EXEC            SELECT Clinician       colsWithNoNulls             FROM                       SELECT DISTINCT p FullName AS Clinician  h Name  CASE WHEN phl personhospitalloginid IS NOT NULL THEN   Yes   ELSE   No   END AS HasLogin             FROM Person p             INNER JOIN personlicense pl ON pl personid   p personid             INNER JOIN LicenseType lt on lt licensetypeid   pl licensetypeid             INNER JOIN licensetypegroup ltg ON ltg licensetypegroupid   lt licensetypegroupid             INNER JOIN Hospital h ON h StateId   pl StateId             LEFT JOIN PersonHospitalLogin phl ON phl personid   p personid AND phl HospitalId   h hospitalid             WHERE ltg Name     RN   AND                 pl licenseactivestatusid   2 AND                 h Active   1 AND                 h StateId IS NOT NULL           AS Results         PIVOT                       MAX HasLogin              FOR Name IN       cols                p

User · Answer

You have to account for all values in the pivot set  you can accomplish this using a cartesian product   select pivoted   from       select cartesian key1  cartesian key2  isnull relationship  value   nullvalue   as  value      from         select k1 key1  k2 key2       from   select distinct key1 from relationship  k1              select distinct key2 from relationship  k2       cartesian       left outer join relationship on relationship key1   cartesian key1 and  relationship key2   carterisan key2   data   pivot       max data value  for   key2 v1    key2 v2    key2 v3            pivoted

User · Answer

Sometimes it s better to think like a parser  like T-SQL parser  While executing the statement  parser does not have any value in Pivot section and you can t have any check expression in that section  By the way  you can simply use this   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

User · Answer

To modify the results under pivot  you can put the columns in the selected fields and then modify them accordingly  May be you can use DECODE for the columns you have built using pivot function     Kranti A

User · Answer

I have encountered similar problem   The root cause is that  use your scenario for my case   in the  temp table  there is no record for a  CLASS RICE and STATE TX b  CLASS VEGIE and  STATE AZ or STATE CA  So  when MSSQL does pivot for no record   MSSQL always shows NULL for MAX  SUM       aggregate functions   None of above solutions  IsNull  AZ   0   works for me  But I do get ideas from these solutions  Thanks   Sorry  it really depends on the  TEMP table  I can only provide some suggestions  1  Make sure  TEMP table have records for below condition  even Data is null     a  CLASS RICE and STATE TX    b  CLASS VEGIE and  STATE AZ or STATE CA      You may need to use cartesian product  select A    B   from A  B 2  In the select query for  temp  if you need to join any table with WHERE  then would better put where inside another sub select query   Goal is 1   3  Use isnull DATA  0  in  TEMP table  4  Before pivot  make sure you have achieved Goal 1      I can t give an answer to the orginal question  since there is no enough info for  temp table   I have pasted my code as example here  hope this will help others   x000D   x000D  SELECT   FROM   x000D     SELECT eeee id as enterprise id x000D       eeee name AS enterprise name x000D       eeee indicator name x000D       CONVERT varchar 12    isnull eid  date   2019-12-01     23  AS data date x000D       isnull eid value 0  AS indicator value x000D     FROM  select ei id as indicator id  ei name as indicator name  e   FROM tbl enterprise indicator ei  tbl enterprise e  eeee             x000D     LEFT JOIN   select   from tbl enterprise indicator data WHERE  date   2020-01-01   eid x000D       ON  eeee id   eid enterprise id and eeee indicator id   enterprise indicator id x000D      AS P  x000D    PIVOT  x000D      x000D     SUM P indicator value  FOR P indicator name IN TX CA  x000D      AS T  x000D   x000D   x000D

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

Examples related to sql

Examples related to sql-server

Examples related to pivot