MYSQL 销售数据按日期行转换为列

scosure

MYSQL 销售数据按日期行转换为列 语句:
CREATE  PROCEDURE `PROC_GET_SUM_Report`(
tag char(1),
btime varchar(10),
etime varchar(10))
BEGIN
      declare $stm varchar(8000);
      declare $rowcnt int;
      declare $mycnt int;
      declare $i int;
      set $mycnt = 0;
      set $i = 1;
       set $stm='select storeno as 编号,storename as 店名,estorename as 店英文名';
if tag='0' then
select count(distinct workdate) into $rowcnt from xa_dg_temp_emp_sum_cross where DATE_SUB(CURDATE(), INTERVAL 1 MONTH) = date(workdate);
      WHILE $i=$rowcnt DO
         set @i :=0;
          select col from (select (@i := @i +1 ) as iden,CONCAT($stm,',SUM(case workdate when ''',workdate,''' then slmny else 0 end) as ''',workdate,'''') as col from (select distinct workdate from xa_dg_temp_emp_sum_cross where DATE_SUB(CURDATE(), INTERVAL 1 MONTH) = date(workdate)) as tb) as tb1 where iden=$i into $stm;
      Set $i:=$i+1;
      END WHILE;
end if;
if tag='1' then
select count(distinct workdate) into $rowcnt from xa_dg_temp_emp_sum_cross where workdate>=btime and workdate=etime;
      WHILE $i=$rowcnt DO
         set @i :=0;
          select col from (select (@i := @i +1 ) as iden,CONCAT($stm,',SUM(case workdate when ''',workdate,''' then slmny else 0 end) as ''',workdate,'''') as col from (select distinct workdate from xa_dg_temp_emp_sum_cross where workdate>=btime and workdate=etime) as tb) as tb1 where iden=$i into $stm;
      Set $i:=$i+1;
      END WHILE;
end if;
     Set @stm=concat($stm,' from xa_dg_temp_emp_sum_cross where paycode=''01'' group by storeno,storename,estorename order by storeno;');
     prepare s from @stm;
    execute s;
    deallocate prepare s;
        END
原始数据图：

行转换列后效果图:




本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u3/105711/showart_2090658.html