MySQL行转列

图一

图二

行转列

把 图一 的展示效果转换成 图二 的展示效果
1、创建成绩表（GRADE）

CREATE TABLE GRADE (
  ID int NOT NULL AUTO_INCREMENT,
  USER_NAME varchar(20) NOT NULL,
  COURSE varchar(20) NOT NULL,
	COURSE_CODE varchar(20) NOT NULL,
  SCORE float DEFAULT '0',
  PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2、新增测试数据

INSERT INTO GRADE(USER_NAME, COURSE, COURSE_CODE, SCORE) VALUES
("张三", "语文", "Chinese", 98),
("张三", "数学", "math", 86),
("张三", "英语", "English", 92),
("李四", "语文", "Chinese", 76),
("李四", "数学", "math", 89),
("李四", "英语", "English", 32),
("王五", "语文", "Chinese", 78),
("王五", "数学", "math", 91),
("王五", "英语", "English", 56);

3、行转列SQL

SELECT USER_NAME,
    MAX(CASE COURSE_CODE WHEN 'Chinese' THEN score ELSE 0 END ) Chinese,
    MAX(CASE COURSE_CODE WHEN 'math' THEN score ELSE 0 END ) math,
    MAX(CASE COURSE_CODE WHEN 'English' THEN score ELSE 0 END ) English
FROM GRADE
GROUP BY USER_NAME;