mysql列轉行

測試資料：
mysql>create table sale(month int(6),product varchar(32),amount decimal(20,2)
mysql> insert into sale values(201502,'apple',3500.00);
Query OK, 1 row affected (0.01 sec)
mysql> insert into sale values(201502,'banana',3000.00);
Query OK, 1 row affected (0.01 sec)
mysql> insert into sale values(201502,'pear',1000.00);
Query OK, 1 row affected (0.01 sec)
mysql> insert into sale values(201502,'orange',1000.00);
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from sale;
+--------+---------+---------+
| month  | product | amount   |
+--------+---------+---------+
| 201502 | apple   | 3500.00 |
| 201502 | banana  | 3000.00 |
| 201502 | pear    | 1000.00 |
| 201502 | orange  | 1000.00 |
+--------+---------+---------+
4 rows in set (0.00 sec)
現在前端展現要求需要以product為行的資料，具體實現如下：
SELECT 
month,
max(case when product='apple' then mount ELSE 0 END) apple,
max(case when product='banana' then mount ELSE 0 END) banana,
max(case when product='pear' then mount ELSE 0 END) pear,
max(case when product='orange' then mount ELSE 0 END) orange
FROM sale GROUP BY month;

+--------+---------+---------+---------+---------+
| month  | apple   | banana  | pear    | orange  |
+--------+---------+---------+---------+---------+
| 201502 | 3500.00 | 3000.00 | 1000.00 | 1000.00 |
+--------+---------+---------+---------+---------+
1 row in set (0.00 sec)
注：GROUP BY之後一定要用max函式，不然會出現banana，pear，orange會出現空
原因：
SELECT 
month,
case when product='apple' then mount ELSE 0 END apple,
case when product='banana' then mount ELSE 0 END banana,
case when product='pear' then mount ELSE 0 END pear,
case when product='orange' then mount ELSE 0 END orange
FROM sale 結果是：
+--------+---------+---------+---------+---------+
| month  | apple   | banana  | pear    | orange  |
+--------+---------+---------+---------+---------+
| 201502 | 3500.00 |       0 |       0 |       0 |
| 201502 |       0 | 3000.00 |       0 |       0 |
| 201502 |       0 |       0 | 1000.00 |       0 |
| 201502 |       0 |       0 |       0 | 1000.00 |
+--------+---------+---------+---------+---------+
4 rows in set (0.00 sec)
group by後只會取第一條記錄，除了apple其他欄位都是0，所以要加個max，把每個欄位的最大值取出來。