MySQL Dynamic row to column

Creating a source table and inserting source data

DROP TABLE IF EXISTS ' score '; CREATE TABLE ' score ' (  int(one) not NULL auto_increment,  'class' varchar (255) default NULL,  double  default NULL,  int(  One default NULL,  PRIMARY KEY (' id ')) ENGINE=innodb auto_increment=8 DEFAULT charset=  Latin1;

INSERT into ' score ' VALUES ('1','Math',' -','1'INSERT into ' score ' VALUES ('2','中文版',' -','1'INSERT into ' score ' VALUES ('3','Computer',' the','1'INSERT into ' score ' VALUES ('4','Sports',' -','1'INSERT into ' score ' VALUES ('5','Math',' the','2'INSERT into ' score ' VALUES ('6','中文版',' -','2'INSERT into ' score ' VALUES ('7','Computer',' -','2');

Original table record

Result records that need to be queried

Query statement:

SET @EE ="';Set@str_tmp ="'; SELECT @EE:=concat (@EE,'SUM (IF (class=\ '',class,'\ '',', score,0)) as',class,',') asAA into @str_tmp from (SELECT DISTINCTclassFrom score) A order by length (AA) desc limit1; SET @QQ=concat ('SELECT ifnull (score.userid,\ ' total\ '),', left (@str_tmp, Char_length (@str_tmp)-1),', SUM (score) as total from score GROUP by UserID with ROLLUP'); PREPARE stmt from @QQ; EXECUTE stmt;d eallocate prepare stmt;

MySQL Dynamic row to column