### 数据表DDL语句:
CREATE TABLE `test_row_to_column` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL DEFAULT '' COMMENT '地区名',
`year` int(5) NOT NULL DEFAULT '0' COMMENT '年份',
`number` int(11) NOT NULL DEFAULT '0' COMMENT '数值',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
### test_row_to_column表测试数据:
### 需求行转列展示结果如下:
### 对应的sql语句实现如下:
SELECT name,
MAX(CASE year WHEN 2019 THEN number ELSE 0 END ) y2019,
MAX(CASE year WHEN 2018 THEN number ELSE 0 END ) y2018,
MAX(CASE year WHEN 2017 THEN number ELSE 0 END ) y2017,
MAX(CASE year WHEN 2016 THEN number ELSE 0 END ) y2016
FROM test_row_to_column
GROUP BY name;