Transpose Table Mysql

Question

I have data like this result from my query Query :

SELECT name, id, Status, COUNT(*) as Result 
FROM `pks_developer` d, pks_mitra m 
    LEFT JOIN status_alpro ON m.id_status = status_alpro.id_status 
    WHERE d.id_pks_deventer code here = m.id_pks_dev 
    GROUP BY developer, m.id_status

Result :

Name    Status    Count    
AB      A         1    
AL      B         1
BD      UP        1
BD      PD        1
DA      PM        1
DA      PD        1
DH      UP        1

But I want to transpose it like :

Name    A   B   UP  PD  PM
AB      1   0   0   0   0
AL      0   1   0   0   0
BD      0   0   1   1   0
DA      0   0   0   1   1
DH      0   0   1   0   0

I've tried with this query, but it didn't work:

SELECT name,
        MAX(CASE WHEN id = 0 THEN Result END) A,
        MAX(CASE WHEN id = 1 THEN Result END) B,
        MAX(CASE WHEN id = 2 THEN Result END) UP,
        MAX(CASE WHEN id = 3 THEN Result END) PD,
        MAX(CASE WHEN id = 4 THEN Result END) PM
    FROM (
        SELECT name, id, Status, COUNT(*) as Result 
        FROM `pks_developer` d, pks_mitra m 
            LEFT JOIN status_alpro ON m.id_status = status_alpro.id_status 
            WHERE d.id_pks_dev = m.id_pks_dev 
            GROUP BY developer, m.id_status
        )s GROUP BY name

The result like this :

Name    A   B   UP  PD  PM
DH      0   0   1   0   0

Any wrong with my query?

Answer 1

You can try below query, as results were not checked so please let me know if there is any issue, so that I can correct.

SELECT NAME, id, IFNULL(COUNT(IF STATUS='A',id,NULL),0) AS 'A', IFNULL(COUNT(IF STATUS='B',id,NULL),0) AS 'B', IFNULL(COUNT(IF STATUS='UP',id,NULL),0) AS 'UP', IFNULL(COUNT(IF STATUS='PD',id,NULL),0) AS 'PD',IFNULL(COUNT(IF STATUS='PM',id,NULL),0) AS 'PM'  
FROM `pks_developer` d JOIN pks_mitra m ON d.id_pks_dev = m.id_pks_dev 
LEFT JOIN status_alpro ON m.id_status = status_alpro.id_status 
GROUP BY developer, m.id_status;