mysql数据的行列转换

1. 动态，适用于列不确定情况，
   
   第一种：
   SET @EE='';   
    SELECT GROUP_CONCAT('SUM(IF(C2=\'',C2,'\'',',C3,0)) AS ',C2)INTO @EE FROM (SELECT DISTINCT C2 FROM TX) A;
    
    SET @QQ=CONCAT('SELECT ifnull(c1,\'total\') as ''合计'',',@EE,' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP');
    
   PREPARE stmt2 FROM @QQ;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2; 
   
   第二种：
   SET @EE='';  
   SELECT @EE:=CONCAT(@EE,'SUM(IF(C2=\'',C2,'\'',',C3,0)) AS ',C2,',') FROM (SELECT DISTINCT C2 FROM TX) A;
   SET @QQ=CONCAT('SELECT ifnull(c1,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP');

    

2.  利用SUM(IF()) 生成列,直接生成结果不再利用子查询
   mysql> select ifnull(c1,'total'),
       -> sum(if(c2='B1',C3,0)) AS B1,
       -> sum(if(c2='B2',C3,0)) AS B2,
       -> sum(if(c2='B3',C3,0)) AS B3,
       -> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL
       -> from tx
       -> group by C1 with rollup ;