mysql行转列和列转行

-- 行转列
CREATE TABLE `st_grade` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `stu_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `course` varchar(20) DEFAULT NULL,
  `score` float(4,1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
INSERT INTO `st_grade` VALUES ('1', '张三', '语文', '86.0')
,('2', '张三', '数学', '90.0')
,('3', '张三', '英语', '75.0')
,('4', '李四', '语文', '92.0')
,('5', '李四', '数学', '93.0')
,('6', '李四', '英语', '96.0')
,('7', '王五', '语文', '82.0')
,('8', '王五', '数学', '71.0')
,('9', '王五', '英语', '74.0');

select stu_name ,
max(case course when '语文' then score else 0 end ) 语文,
max(case course when '数学' then score else 0 end ) 数学,
max(case course when '英语' then score else 0 end ) 英语
from st_grade
group by stu_name;


-- 列转行
CREATE TABLE `st_grade1` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `stu_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `course1` float(4,1) DEFAULT NULL,
  `course2` float(4,1) DEFAULT NULL,
  `course3` float(4,1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;


insert into `st_grade1` (id,stu_name,course1,course2,course3) values(1,'张三', 86.0, 90.0, 75.0),(2,'李四', 92.0, 93.0, 96.0),(3,
'王五', 82.0, 71.0, 74.0);

select stu_name, '语文', course1 score from st_grade1 
union select stu_name, '数学', course2 score from st_grade1 
union select stu_name, '英语', course3 score from st_grade1 
order by stu_name;