不固定列交叉表的实现

 

笔者最近在项目中遇到要做交叉表出报表的情况,也许有人会说,用水晶报表不就可以搞定,但要求总是变化的,并且用表的形式查询出来后,还可以保存到excel中,这样客户自己可以下载下来进行处理。

前言就说道这里,下面说说具体需求。

我是做财务软件的,现在用户要统计每个部门的费用,报表样张如下:

费用类型      生产部     财务部       销售部     装备部      动能部      热轧厂  。。。。

工资             3300     234           435        7654      876        87654

差旅费          45         56             2345       6567     3245      43567

 

 

这些数据在数据库的存储是这样的

会计期间      费用类型     部门      金额

200909     工资            生产部    3300

。。。。。

 

这里说明的是,并不是每个月每个部门都要发生所有的费用,这样报表里的列就不固定。具体的报表列根据当月实际发生的情况来决定。

固定列的交叉报表网上应该很多了,但不固定列的交叉报表就比较少。正是在这种情况下,笔者写了个函数,用来生成查询的sql语句,根据这个sql语句来查询出来交叉报表效果的sql语句。

下面就把函数的源码贴出来(oracle10g,  9i、8i理论上应该也支持只是没测试过)

CREATE OR REPLACE FUNCTION getCrossTabSql(tablename  in varchar2,left_colname in varchar2,right_colname  in varchar2,value_colname  in varchar2)
 RETURN varchar2 IS
 /*
 tablename 可以是个sql结构语句,如(select * from table1)A 如果要求对列排序,就在这里order对应的列
 left_colname 所有在报表左边的列,可以是       column1,column2  这种组合的字符串,行排序依据
 right_colname 就是列的名,当为第二种情况时,可以是column1||column2这种组合的字符串,列排序依据
 value_colname就是要合并的值,当第一种情况时,为空

两种类型的交叉报表

姓名    所选课程1   选课2   选课3

张三                 数学
李四      语文               英语
王五                         英语


姓名    语文       数学      英语

张三               100
李四     60                   80
王五                          70


 */

  strsql varchar2(4000);
  max_col_count number;
 
  stmt_str varchar2(2000);
  cur_hdl int;
  rows_processed int;
  V_DM  VARCHAR2(200);

BEGIN

  strsql:='';
  max_col_count:=0;

  cur_hdl := dbms_sql.open_cursor;
  stmt_str := 'select distinct '||right_colname||' from '||tablename||' order by '||right_colname;
  dbms_sql.parse(cur_hdl, stmt_str, 1);  
 -- dbms_sql.bind_variable( cur_hdl, right_colname, 1080);
 
  dbms_sql.define_column(cur_hdl, 1, V_DM, 200); 
 
  rows_processed := dbms_sql.execute(cur_hdl);
 
  LOOP   
    IF dbms_sql.fetch_rows(cur_hdl) > 0 then 
      max_col_count:=max_col_count+1;     
      dbms_sql.column_value(cur_hdl, 1, V_DM);
      if(length(value_colname)>0 ) then
          strsql:=strsql||',sum(decode('||right_colname||','||''''||V_DM||''''||','||value_colname||',0)) '
          ||replace(replace(V_DM,'-',''),'-','');
      else
          strsql:=strsql||',max(decode(rn,'||to_char(max_col_count)||','||right_colname||',null)) '||'col'||to_char(max_col_count);
      end if;
     ELSE 
       EXIT;  
     END IF;  
  END LOOP;  
  dbms_sql.close_cursor(cur_hdl);
  if(length(strsql)>0) then
    if(length(value_colname)>0 ) then
        strsql:='select '||left_colname||strsql||' from '||tablename||' group by '||left_colname ;
    else    
        strsql:='select '||left_colname||strsql||' from (select '||left_colname||','||right_colname
          ||',row_number() over (partition by '||left_colname||' order by '||right_colname;
        strsql:=strsql||' )rn from '||tablename||') group by '||left_colname ;
    end if;
  end if;  
  RETURN(strsql);
 END getCrossTabSql;

/*

备注:在调试时,如果返回的string太长,要用long型来调试。这个返回的sql长度,很大程序是决定于您的数据库数据的。

阅读更多
个人分类: sql
想对作者说点什么? 我来说一句

COGNOS设置隐藏列

FA

chenjian98306 chenjian98306

2016-09-05 11:02:40

阅读数:316

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭