Fields as result set headers in mysql 8

Below is the snippet of my data,

empid   CategoryName               Type    value
1      Accommodation Deduction   DEDUCTION  -100.0000
1      Canteen Deduction         DEDUCTION  -200.0000
1      Housing                   ADDITION   3000.0000
1      Transport Allowance       ADDITION   2000.0000

empid would be employee id and there are other employee id’s in the dataset but all will have similar data.

My aim to to show the various pay categories as columns in the query result like,

Accommodation Deduction   Canteen Deduction   Housing          Transport Allowance
   -100.0000                 -200.0000        3000.0000          2000.0000

I am using the below query,

select 
     MAX(CASE
        WHEN CategoryName = 'Accommodation Deduction' THEN pi.value
        ELSE 0
    END) AS 'Accommodation Deduction',
     MAX(CASE
        WHEN CategoryName = 'Canteen Deduction' THEN pi.value
        ELSE 0
    END) AS 'Canteen Deduction',
     MAX(CASE
        WHEN CategoryName = 'Housing' THEN pi.value
        ELSE 0
    END) AS 'Housing',
     MAX(CASE
        WHEN CategoryName = 'Transport Allowance' THEN pi.value
        ELSE 0
    END) AS 'Transport Allowance' from Report pi group by empid;

The deduction category are showing value as zero which should not be,

Accommodation Deduction   Canteen Deduction     Housing         Transport Allowance
       0.0000               0.0000              3000.0000          2000.0000

dbfiddle

Answer

There are columns whose values are negative, so the conditionl MAX() returns 0, because of the ELSE 0 branch of the CASE statement.

You can turn these to MIN()s:

select empid
     MIN(CASE WHEN CategoryName = 'Accommodation Deduction' THEN pi.value ELSE 0 END) AS Accommodation_Deduction,
     MIN(CASE WHEN CategoryName = 'Canteen Deduction'       THEN pi.value ELSE 0 END) AS Canteen_Deduction,
     MAX(CASE WHEN CategoryName = 'Housing'                 THEN pi.value ELSE 0 END) AS Housing,
     MAX(CASE WHEN CategoryName = 'Transport Allowance'     THEN pi.value ELSE 0 END) AS Transport_Allowance 
from Report pi 
group by empid;