MySQL 数据透视表

Question

我有数据。

   A,   STATUS,  P
   A1,    1,     P1
   A1,    1,     P2
   A1,    1,     P3
   A2,    1,     P3
   A2,    1,     P4
   A2,    1,     P5
   A3,    0,     NULL

我想要相同的结果

   P,   A1,  A2,  A3
   P1,  1,   0,   0
   P2,  1,   0,   0
   P3,  1,   1,   0
   P4,  0,   1,   0
   P5,  0,   1,   0

如何使用 mysql 查询来完成？

Answer 1

试试这个；)

select
    `P`,
    max(if(`A` = 'A1', `STATUS`, 0)) as `A1`,
    max(if(`A` = 'A2', `STATUS`, 0)) as `A2`,
    max(if(`A` = 'A3', `STATUS`, 0)) as `A3`
from table1
where `P` IS NOT NULL
group by `P`

DEMO HERE

已编辑:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(`A` = ''',
      `A`,
      ''', `STATUS`, 0)) AS ',
      `A`
    )
  ) INTO @sql
FROM table1;
SET @sql = CONCAT('SELECT `P`, ', @sql, ' FROM table1 WHERE `P` IS NOT NULL GROUP BY `P`');

PREPARE stmt FROM @sql;
EXECUTE stmt;

DEMO HERE