数据库--mysql中动态行转列

create table table1(
 col1 char(2),
 col2 char(2),
 col3 int
);




insert into table1 values
('A1','B1',9),
('A2','B1',7),
('A3','B1',4),
('A4','B1',2),
('A1','B2',2),
('A2','B2',9),
('A3','B2',8),
('A4','B2',5),
('A1','B3',1),
('A2','B3',8),
('A3','B3',8),
('A4','B3',6),
('A1','B4',8),
('A2','B4',2),
('A3','B4',6),
('A4','B4',9),
('A1','B4',3),
('A2','B4',5),
('A3','B4',2),

('A4','B4',5);

SET @EE='';
SELECT @EE:=CONCAT(@EE,'SUM(IF(col2=\'',col2,'\'',',col3,0)) AS ',col2,',') FROM table1  order by col4) A;
SET @QQ=CONCAT('SELECT ifnull(col1,\'total\') AS sitc4,',LEFT(@EE,LENGTH(@EE)-1),'  FROM  table1  GROUP BY col1 WITH ROLLUP');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;