mysql 动态行转列

前言： mysql的行转列并没有mssql中的pivot

测试数据：

DROP TABLE IF EXISTS `mytest`;CREATE TABLE `mytest` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `class` varchar(255) DEFAULT NULL,  `score` double DEFAULT NULL,  `userid` int(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;INSERT INTO `mytest` VALUES ('1', 'math', '90', '1');INSERT INTO `mytest` VALUES ('2', 'english', '90', '1');INSERT INTO `mytest` VALUES ('3', 'computer', '80', '1');INSERT INTO `mytest` VALUES ('4', 'sports', '90', '1');INSERT INTO `mytest` VALUES ('5', 'math', '80', '2');INSERT INTO `mytest` VALUES ('6', 'english', '85', '2');INSERT INTO `mytest` VALUES ('7', 'computer', '100', '2');

语句：

SET @EE='';    SELECT @EE:=CONCAT(@EE,'SUM(IF(class=\'',class,'\'',',score,0)) AS ',class,',')     FROM (SELECT DISTINCT class FROM mytest) A;SET @QQ=CONCAT('SELECT mytest.userid AS columnA,',LEFT(@EE,LENGTH(@EE)-1),'     FROM mytest GROUP BY userid');PREPARE stmt2 FROM @QQ;

PS: 如果将这块sql封装成存储过程，会返回2个结果集，一个sql语句，还有一个查询结果。
所以如果是存储过程的话，推荐这么实用：

#drop PROCEDURE sp_mytestcreate PROCEDURE sp_mytest()begin SET @EE='';    SELECT b.* into @EE from ( SELECT @EE:=CONCAT(@EE,'SUM(IF(class=\'',class,'\'',',score,0)) AS ',class,',') col    FROM (SELECT DISTINCT class FROM mytest) A) b ORDER BY b.col desc limit 0,1 ;SET @QQ=CONCAT('SELECT mytest.userid AS columnA,',LEFT(@EE,LENGTH(@EE)-1),'     FROM mytest GROUP BY userid');PREPARE stmt2 FROM @QQ;EXECUTE stmt2;endcall sp_mytest()