交叉表 sql

1.静态交叉表（mysql）

         SELECT FItemID, (CASE WHEN   FName= '生产部' THEN FAuxQty ELSE 0 END) AS '生产部', 
      (CASE  WHEN  FName= '包装车间' THEN FAuxQty ELSE 0 END) AS '包装车间', 
      FStockID, FDeptID
FROM testtable

-----------------------------------------------------------------------------------------------------

 

http://blog.csdn.net/ACMAIN_CHM/archive/2009/06/19/4283943.aspx

 

SELECT
         IFNULL(c1,'total') AS 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(IF(c2='total',c3,0)) AS total
     FROM (
         SELECT c1,IFNULL(c2,'total') AS c2,SUM(c3) AS c3
        FROM tx
         GROUP BY c1,c2
         WITH ROLLUP                            //这个似乎能汇总 列上的汇总
         HAVING c1 IS NOT NULL
     ) AS A
     GROUP BY c1
     WITH ROLLUP;

 

(2)

select c1,
    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
     UNION
    SELECT '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) FROM TX
     ;

 

(3)

   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 ;

 

（4） 动态，适用于列不确定情况

       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');
    PREPARE stmt2 FROM @QQ;
    EXECUTE stmt2;
-----------------------------------------------------------------