sql列转行

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for studentscores
-- ----------------------------
DROP TABLE IF EXISTS `studentscores`;
CREATE TABLE `studentscores` (
  `UserName` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `Subject` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
  `Score` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of studentscores
-- ----------------------------
INSERT INTO `studentscores` VALUES (‘Nick‘, ‘语文‘, ‘80‘);
INSERT INTO `studentscores` VALUES (‘Nick‘, ‘数学‘, ‘90‘);
INSERT INTO `studentscores` VALUES (‘Nick‘, ‘英语‘, ‘70‘);
INSERT INTO `studentscores` VALUES (‘Nick‘, ‘生物‘, ‘85‘);
INSERT INTO `studentscores` VALUES (‘Kent‘, ‘语文‘, ‘80‘);
INSERT INTO `studentscores` VALUES (‘Kent‘, ‘数学‘, ‘90‘);
INSERT INTO `studentscores` VALUES (‘Kent‘, ‘英语‘, ‘70‘);
INSERT INTO `studentscores` VALUES (‘Kent‘, ‘生物‘, ‘85‘);

select 
      username, 
      max(case subject when ‘语文‘ then score else 0 end) as ‘语文‘,
      max(case subject when ‘数学‘ then score else 0 end) as ‘数学‘,
      max(case subject when ‘英语‘ then score else 0 end) as ‘英语‘,
      max(case subject when ‘生物‘ then score else 0 end) as ‘生物‘
from studentscores
group by username