转置表Mysql

Question

我从我的查询查询中得到了如下数据：

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

结果：

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

但我想将其转置如下：

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

我已经尝试了这个查询，但它不起作用：

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

结果如下所示：

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

我的查询有什么问题吗？

Answer 1

你可以尝试下面的查询，因为没有检查结果，所以如果有任何问题，请让我知道，以便我可以更正。

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;