MySQL 动态行转列

CREATE TABLE `tbl01` (
  `id` INT(11) DEFAULT NULL,
  `kemu` VARCHAR(20) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

INSERT INTO tbl01(id, kemu) VALUES ('1', '语文');
INSERT INTO tbl01(id, kemu) VALUES ('2', '数学');
INSERT INTO tbl01(id, kemu) VALUES ('3', '英语');
INSERT INTO tbl01(id, kemu) VALUES ('4', '物理');
INSERT INTO tbl01(id, kemu) VALUES ('5', '化学');

CREATE TABLE `tbl02` (
  `nid` VARCHAR(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `kemu` VARCHAR(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `chji` INT(11) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

INSERT INTO tbl02 (nid, kemu, chji) VALUES ('李明', '1', '95');
INSERT INTO tbl02 (nid, kemu, chji) VALUES ('李明', '2', '89');
INSERT INTO tbl02 (nid, kemu, chji) VALUES ('李明', '4', '74');
INSERT INTO tbl02 (nid, kemu, chji) VALUES ('张三', '1', '81');
INSERT INTO tbl02 (nid, kemu, chji) VALUES ('张三', '3', '65');
INSERT INTO tbl02 (nid, kemu, chji) VALUES ('张三', '5', '38');

SET @sql := 'SELECT nid';
SELECT @sql := CONCAT(@sql, ', max(case kemu when ', id, ' then chji else 0 end) as ', kemu)
FROM (SELECT DISTINCT id, kemu FROM tbl01) AS a;
SELECT @sqlstr := CONCAT(@sql, ' from tbl02 group by nid');
PREPARE rc FROM @sqlstr;

EXECUTE rc;

CREATE  TABLE  `tbl03` (

   `id`  int (11)  DEFAULT  NULL ,

   `xm`  varchar (20)  COLLATE  utf8_unicode_ci  DEFAULT  NULL ,

   `nj`  int (11)  DEFAULT  NULL ,

   `km`  varchar (20)  COLLATE  utf8_unicode_ci  DEFAULT  NULL ,

   `cj`  int (11)  DEFAULT  NULL

) ENGINE=InnoDB  DEFAULT  CHARSET=utf8  COLLATE =utf8_unicode_ci

统计每个人各年级的总成绩：

 SET @sql := 'SELECT xm';
 SELECT @sql := CONCAT(@sql, ', sum(case nj when ', nj, ' then cj else 0 end) as `', nj, '年级`')
 FROM (SELECT nj FROM tbl03 GROUP BY nj) AS a;
 SELECT @sqlstr := CONCAT(@sql, ' from tbl03 group by xm');
 PREPARE rc FROM @sqlstr;
 EXECUTE rc;