mysql 8 pivot query should return a non null value

I would like the following pivot query to show value 0 instead of null,

SELECT 
    pi.employeeId,
    pi.Id,
    MAX(CASE
        WHEN pi.category = 'Repayment' THEN pi.value
        WHEN isnull(pi.category) = 1 then 0
        -- ELSE 0
    END) as 'Repayment',
     MAX(CASE
        WHEN pi.category = 'Salary' THEN pi.value
        ELSE 0
    END) as 'Salary',
     MAX(CASE
        WHEN pi.category = 'Allowance' THEN pi.value
        ELSE 0
    END) as 'Allowance'
FROM
   payData pi
GROUP BY pi.employeeId , pi.Id ;

Output for the above is,

employeeId     Id      Repayment    Salary  Allowance
  121          2           2000     15000     1000
  122          2           null     20000     2000

Employee id 122 does not have a Repayment value so the desired output is,

employeeId     Id      Repayment    Salary  Allowance
    121        2         2000       15000     1000
    122        2           0        20000     2000

dbfiddle

Answer

I don’t see the need for the second branch of the repayment case. If you want 0 when the category is not available, just else 0:

SELECT 
    employeeId,
    Id,
    MAX(CASE WHEN category = 'Repayment' THEN value ELSE 0 END) as Repayment,
    MAX(CASE WHEN category = 'Salary'    THEN value ELSE 0 END) as Salary,
    MAX(CASE WHEN category = 'Allowance' THEN value ELSE 0 END) as Allowance
FROM payData pi
GROUP BY employeeId, Id;

Notes:

• Don’t use single quotes for identifiers! They should be used for literal strings only, as specified in ANSI SQL and supported in all databases.

• You have a mono-table query, so prefixing all column names is not mandatory