不固定列交叉表的实现

最新推荐文章于 2023-05-19 16:47:41 发布

hghc023

最新推荐文章于 2023-05-19 16:47:41 发布

阅读量680

点赞数

分类专栏： sql 文章标签：报表 oracle10g sql 数据库 excel function

sql 专栏收录该内容

3 篇文章

订阅专栏

笔者最近在项目中遇到要做交叉表出报表的情况，也许有人会说，用水晶报表不就可以搞定，但要求总是变化的，并且用表的形式查询出来后，还可以保存到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

姓名所选课程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;