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),
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;