mysql-行转列

现要求将图1的数据，使用sql查询后，得到图2的结果

图1：

图2：

构造数据：

CREATE TABLE `t_score` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `course` varchar(255) DEFAULT NULL,
  `score` int(10) DEFAULT NULL,
  `age` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `bookstore`.`t_score` (`id`, `name`, `course`, `score`, `age`) VALUES ('1', '张三', 'Linux', '85', '一');
INSERT INTO `bookstore`.`t_score` (`id`, `name`, `course`, `score`, `age`) VALUES ('2', '张三', 'Sql', '90', '二');
INSERT INTO `bookstore`.`t_score` (`id`, `name`, `course`, `score`, `age`) VALUES ('3', '张三', 'Java', '95', '三');
INSERT INTO `bookstore`.`t_score` (`id`, `name`, `course`, `score`, `age`) VALUES ('4', '李四', 'Linux', '79', '四');
INSERT INTO `bookstore`.`t_score` (`id`, `name`, `course`, `score`, `age`) VALUES ('5', '李四', 'Sql', '75', '五');
INSERT INTO `bookstore`.`t_score` (`id`, `name`, `course`, `score`, `age`) VALUES ('6', '李四', 'Java', '95', '六');
INSERT INTO `bookstore`.`t_score` (`id`, `name`, `course`, `score`, `age`) VALUES ('7', '王五', 'Linux', '94', '七');
INSERT INTO `bookstore`.`t_score` (`id`, `name`, `course`, `score`, `age`) VALUES ('8', '王五', 'Sql', '83', '八');
INSERT INTO `bookstore`.`t_score` (`id`, `name`, `course`, `score`, `age`) VALUES ('9', '王五', 'Java', '75', '九');

思路：先按名字分组，再对需要展示的列进行聚合

select 
	`name`,
	SUM(if(course='Linux',score,null)) as 'Linux',
	SUM(if(course='Sql',score,null)) as 'Sql',
	SUM(if(course='Java',score,null)) as 'Java'
from t_score
group by `name`;

应题目要求，score是数字类型的，我们可以使用sum来聚合

如果需要字符串类型的`age`进行聚合，我们可以使用group_concat来达到相同的效果

select 
	`name`,
	group_concat(if(course='Linux',age,null)) as 'Linux',
	group_concat(if(course='Sql',age,null)) as 'Sql',
	group_concat(if(course='Java',age,null)) as 'Java'
from t_score
group by `name`;

附加另一种解决方法：（sql太长了）

select 
	`name`,
	max(`Linux`) as 'Linux',
	max(`Sql`) as 'Sql',
	max(`Java`) as 'Java'
from(
	select
			`name`,
			case 
					when course='Linux' then score
			end as 'Linux',
			case 
					when course='Sql' then score
			end as 'Sql',
			case 
					when course='Java' then score
			end as 'Java'
	from t_score
	group by `name`,course
)	a group by `name`;

--