mysql交叉表，行列动态转换

http://blog.csdn.net/ACMAIN_CHM/article/details/4283943

create table aaa(
 id int primary key,
 c1 char(2),
 c2 char(2),
 c3 int
);

 

insert into aaa values
(1 ,'A1','B1',9),
(2 ,'A2','B1',7),
(3 ,'A3','B1',4),
(4 ,'A4','B1',2),
(5 ,'A1','B2',2),
(6 ,'A2','B2',9),
(7 ,'A3','B2',8),
(8 ,'A4','B2',5),
(9 ,'A1','B3',1),
(10 ,'A2','B3',8),
(11 ,'A3','B3',8),
(12 ,'A4','B3',6),
(13 ,'A1','B4',8),
(14 ,'A2','B4',2),
(15 ,'A3','B4',6),
(16 ,'A4','B4',9),
(17 ,'A1','B4',3),
(18 ,'A2','B4',5),
(19 ,'A3','B4',2),
(20 ,'A4','B4',5);

mysql---- 中运行命令：
SET @EE="";
SELECT @EE:=CONCAT(@EE,"SUM(IF(C2='",C2,"'",",C3,0)) AS ",C2,",") FROM (SELECT DISTINCT C2 FROM aaa) A;
SET @QQ=CONCAT("SELECT ifnull(c1,'total'),",LEFT(@EE,LENGTH(@EE)-1)," ,SUM(C3) AS TOTAL FROM aaa GROUP BY C1 WITH ROLLUP");
PREPARE stmt2 FROM @QQ;
 EXECUTE stmt2;