mysql - 如何在mysql中旋转两个值？

Question

我有以下表结构:

+-------+----------+------+------------+------------+
| agent | product  | type | value_2013 | value_2014 |
+-------+----------+------+------------+------------+
| John  | product1 | A    |         10 |         11 |
| John  | product1 | C    |         14 |         13 |
| Mike  | product1 | A    |         11 |         20 |
| Mike  | product2 | C    |         13 |         15 |
+-------+----------+------+------------+------------+

类型始终为 A 或 C我需要在这样的表格中进行转换(透视)

 agent, product, type, value_2013_A, value_2013_C, value_2014_A, value_2014_C

 ...
 ...

我已经编写了以下 SQL 查询，但它不起作用。它只需要第一种类型

SELECT agent,product,
 case when type='C' then value_2013 else 0 end as value_2013_C,   <-- take this value,ok!
 case when type='C' then value_2013 else 0 end as value_2013_A,    <-- but obviously not take this value
 case when type='A' then impap else 0 end as value_2014_A,        <-- take this value, ok!
 case when type='A' then impac else 0 end as value_2014_C          <-- but obviously not take this value
    FROM  mytable 
    GROUP BY agent,product;

如何修改？

Answer 1

你忘记了最大。尝试一下

 SELECT agent,product,
 max(case when type='C' then value_2013 else 0 end) as value_2013_C,   
 max(case when type='C' then value_2013 else 0 end) as value_2013_A,    
 max(case when type='A' then impap else 0 end) as value_2014_A,        
 max(case when type='A' then impac else 0 end ) as value_2014_C         
 FROM  mytable 
 GROUP BY agent,product;