mysql - 转置表 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;