Mysql行转列

问题

今天看到一个mysql行转列的问题

将表1 转换为 表2

表1

year	month	amount
2011	1	1.1
2011	2	1.2
2011	3	1.3
2011	4	1.4
2012	1	2.1
2012	2	2.2
2012	3	2.3
2012	4	2.4

表2

year	m1	m2	m3	m4
2011	1.1	1.2	1.3	1.4
2012	2.1	2.2	2.3	2.4

方法

百度获得以下两种方法

CASE WHEN

SELECT 
    `year`,
    MAX( CASE WHEN `month` = 1 THEN amount ELSE 0 END) as "m1",
    MAX( CASE WHEN `month` = 2 THEN amount ELSE 0 END) as "m2", 
    MAX( CASE WHEN `month` = 3 THEN amount ELSE 0 END) as "m3",
    MAX( CASE WHEN `month` = 4 THEN amount ELSE 0 END) as "m4"
FROM
    test
GROUP BY `year`;

GROUP_CONCAT

SELECT 
    year, 
    SUBSTRING_INDEX(SUBSTRING_INDEX(str, ",", -4),",",1) as m1 ,
    SUBSTRING_INDEX(SUBSTRING_INDEX(str, ",", -3),",",1) as m2 ,
    SUBSTRING_INDEX(SUBSTRING_INDEX(str, ",", -2),",",1) as m3 ,
    SUBSTRING_INDEX(SUBSTRING_INDEX(str, ",", -1),",",1) as m4 
from
(SELECT year, GROUP_CONCAT(amount) as str FROM test GROUP BY `year`) as t;

参考文档

1. http://hchmsguo.iteye.com/blog/555543
2. https://www.cnblogs.com/crystaltu/p/6699392.html
3. https://www.oschina.net/question/3937294_2285120