MySql表数据行列转换

USE test;
CREATE TABLE student(
 id VARCHAR(20) NOT NULL,
 subjects VARCHAR(20) NOT NULL,
 grade INTEGER NOT NULL DEFAULT 0
);

INSERT INTO student VALUES('student1','语文',80);
INSERT INTO student VALUES('student1','数学',70);
INSERT INTO student VALUES('student1','英语',60);
INSERT INTO student VALUES('student2','语文',90);
INSERT INTO student VALUES('student2','数学',80);
INSERT INTO student VALUES('student2','英语',100);

COMMIT;

SELECT * FROM student;

id subjects grade
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100

/* 行列转换*/
SELECT  
 id,
 SUM(IF(subjects='语文',grade,NULL)) '语文',
 SUM(IF(subjects='数学',grade,NULL)) '数学',
 SUM(IF(subjects='英语',grade,NULL)) '英语'
FROM
 student
GROUP BY
 id
;

id 语文 数学 英语
student1 80 70 60
student2 90 80 100