SQL竖列变横列

DROP TABLE IF EXISTS curriculumTable;

CREATE TABLE curriculumTable (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20),
  curriculum VARCHAR(20)
) CHARSET=utf8;

INSERT INTO curriculumTable (NAME,curriculum) VALUES ('张三', '语文');
INSERT INTO curriculumTable (NAME,curriculum) VALUES ('张三', '数学');
INSERT INTO curriculumTable (NAME,curriculum) VALUES ('张三', '生物');
INSERT INTO curriculumTable (NAME,curriculum) VALUES ('李四', '语文');
INSERT INTO curriculumTable (NAME,curriculum) VALUES ('李四', '生物');
INSERT INTO curriculumTable (NAME,curriculum) VALUES ('王五', '数学');
INSERT INTO curriculumTable (NAME,curriculum) VALUES ('王五', '生物');


SELECT  * FROM curriculumTable ;


SELECT id,MAX(CASE curriculum WHEN '语文' THEN NAME ELSE NULL END ) 语文,MAX(CASE curriculum WHEN '数学' THEN NAME ELSE NULL END ) 数学,MAX(CASE curriculum WHEN '生物' THEN NAME ELSE NULL END ) 生物 FROM curriculumTable  GROUP BY id

 

转载于:https://www.cnblogs.com/luoruiyuan/p/9132064.html