MySQL row-to-column and column-changing career

Row to Column

Example: Convert figure 1 to Figure 2 result display

Figure 1

Figure 2

CREATE TABLE ' Test_tb_grade ' (  ' ID ' int () NOT NULL auto_increment,  ' user_name ' varchar () DEFAULT NULL,  ' COURSE ' varchar ' default NULL,  ' score ' float default ' 0 ',  PRIMARY KEY (' ID ')) Engine=innodb auto_increment=1 D Efault Charset=utf8;

INSERT INTO Test_tb_grade (user_name, COURSE, score) VALUES ("Zhang San",  "math", 34), ("Zhang San", "language", 58), ("Zhang San", "English", 58), ("John Doe", " Mathematics ", 45), (" John Doe "," language ", 87), (" John Doe "," English ", 45), (" Harry "," Mathematics ", 76), (" Harry "," language ", 34), (" Harry "," English ", 89);

　　

Row to column sql:

SELECT user_name,    Max (case course when ' math ' then score else 0 END) math,    Max (case course when ' language ' then score ELSE 0 end) Language,    MAX (case course when ' English ' then score ELSE 0 END) English from Test_tb_gradegroup by user_name;

　　

Row career

Example: Convert figure 1 to Figure 3 result display

Figure 3

Column Career sql:

Select user_name, ' language ' COURSE, Cn_score as score from Test_tb_grade2union select user_name, ' Math ' COURSE, math_score as SC ORE from Test_tb_grade2union Select user_name, ' English ' COURSE, En_score as score from Test_tb_grade2order by User_name,course ;

"Reprinted from: http://blog.csdn.net/jx_870915876/article/details/52403472"

MySQL row-to-column and column-changing career