Oracle表或者视图的行转列[练习]

最新推荐文章于 2024-02-01 17:18:00 发布

藏何

最新推荐文章于 2024-02-01 17:18:00 发布

阅读量634

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/u013533810/article/details/37956195

版权

Oracle 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

 --行转列函数 2014年7月17日 16:15:18 
  Function Fn_Row_To_Col(tabname         in varchar2, --需要进行行转列操作的表名或者视图；
                         group_col       in varchar2, --查询结果要按某列或某些列分组的字段名；
                         column_col      in varchar2, -- 要从行转成列的字段；
                         value_col       in varchar2, -- 需要聚合的值字段；
                         Aggregate_func  in varchar2 default 'max', -- 选用的聚合函数，可选，默认为max；
                         colorder        in varchar2 default null, -- 行转列后列的排序，可选；
                         roworder        in varchar2 default null, -- 行转列后记录的排序，可选；
                         when_value_null in varchar2 default null,-- 若value_col字段的值聚合后为空，则转换成该值，可选；
                         where_str in varchar2 default null,--对tabname的查询条件，可选
                         rowcount_str  in varchar2 default null--,--对tabname进行行统计，可选(合计、2099-12-31)
                         )                 
  return sys_refcursor 
   as
    sqlstr varchar2(4000) := 'select ' || group_col || ' ';
    c1     sys_refcursor;
    v1     varchar2(100);
    tempstr varchar2(4000):='';
    rowsqlstr varchar2(4000):='';
    cur    sys_refcursor;
  begin
    If rowcount_str is not null then--如果tabname进行行统计的条件不为空则进行统计
      rowsqlstr:=rowsqlstr||sqlstr||','||column_col||','||value_col||' from '||tabname||' '||where_str||' union '||                           
                           sqlstr||','''||rowcount_str||''' '||column_col||','||Aggregate_func||'('||value_col||') '||value_col||' from '||tabname
                           ||' '||where_str||' group by '||group_col;
      tempstr:=tempstr||'select distinct ' || column_col || ' from ( '|| rowsqlstr||' ) '||(case when colorder is not null then ' order by ' || colorder  end);                    
    Else 
      tempstr:=tempstr||'select distinct ' || column_col || ' from ' || tabname||' '||where_str||(case when colorder is not null then ' order by ' || colorder  end);
    End If;
    
    open c1 for tempstr;
    loop fetch c1  into v1;
      exit when c1%notfound;
      sqlstr := sqlstr || chr(10) || ',' || case when when_value_null is not null then
                   'nvl(' end || Aggregate_func || '(decode(to_char(' || column_col ||'),''' || v1 || ''',' || value_col || '))'
                    || case  when when_value_null is not null then chr(44) || when_value_null || chr(41)  end || '"' || v1 || '"';
    end loop;
    close c1;
    tempstr:=sqlstr || ' from ' || case when rowcount_str is not null then '( '||rowsqlstr||' )' else tabname end||' group by ' || group_col || case when roworder is not null then ' order by ' || roworder end;
    --DBMS_OUTPUT.put_line(length(tempstr));--查看是否超过4000个字符
    open cur for tempstr;
    return cur;
  end Fn_Row_To_Col;

--执行查询
Select
PKG_STM_GETDATA_TABLE.Fn_Row_To_Col(tabname =>'VW_DAYRPT_STDTSK_R',
                                        group_col => 'DPTKEY,DPTCNNAME,PARTKEY,PARTMCODE,PARTNAME,LABOURKEY,LABOURCODE,LABOURNAME',
                                        column_col => 'DAYRPTDATE',
                                        value_col => 'PRODTIME',
                                        aggregate_func =>'Sum',
                                        colorder =>'DAYRPTDATE asc',
                                        roworder =>'DPTKEY asc,LABOURCODE asc,PARTKEY asc',
                                        when_value_null =>null,
                                        where_str => ' where DAYRPTDATE>=''2014-05-28'' and DAYRPTDATE<=''2014-06-28''',
                                        rowcount_str => '合计')                             
from dual;

备注：程序中可以使用存储过程

  Procedure Sp_Row_To_Col(tabname         in varchar2, --需要进行行转列操作的表名或者视图；
                         group_col       in varchar2, --查询结果要按某列或某些列分组的字段名；
                         column_col      in varchar2, -- 要从行转成列的字段；
                         value_col       in varchar2, -- 需要聚合的值字段；
                         Aggregate_func  in varchar2 default 'max', -- 选用的聚合函数，可选，默认为max；
                         colorder        in varchar2 default null, -- 行转列后列的排序，可选；
                         roworder        in varchar2 default null, -- 行转列后记录的排序，可选；
                         when_value_null in varchar2 default null,-- 若value_col字段的值聚合后为空，则转换成该值，可选；
                         where_str in varchar2 default null,--对tabname的查询条件，可选
                         rowcount_str  in varchar2 default null,--对tabname进行行统计，可选(合计、2099-12-31)
                         cur  out  sys_refcursor--以游标的形式、返回数据集
                         ) 
   as
    sqlstr varchar2(4000) := 'select ' || group_col || ' ';
    c1     sys_refcursor;
    v1     varchar2(100);
    tempstr varchar2(4000):='';
    rowsqlstr varchar2(4000):='';
    --cur    sys_refcursor;
  begin
    If rowcount_str is not null then--如果tabname进行行统计的条件不为空则进行统计
      rowsqlstr:=rowsqlstr||sqlstr||','||column_col||','||value_col||' from '||tabname||' '||where_str||' union '||                           
                           sqlstr||','''||rowcount_str||''' '||column_col||','||Aggregate_func||'('||value_col||') '||value_col||' from '||tabname
                           ||' '||where_str||' group by '||group_col;
      tempstr:=tempstr||'select distinct ' || column_col || ' from ( '|| rowsqlstr||' ) '||(case when colorder is not null then ' order by ' || colorder  end);                    
    Else 
      tempstr:=tempstr||'select distinct ' || column_col || ' from ' || tabname||' '||where_str||(case when colorder is not null then ' order by ' || colorder  end);
    End If;
    
    open c1 for tempstr;
    loop fetch c1  into v1;
      exit when c1%notfound;
      sqlstr := sqlstr || chr(10) || ',' || case when when_value_null is not null then
                   'nvl(' end || Aggregate_func || '(decode(to_char(' || column_col ||'),''' || v1 || ''',' || value_col || '))'
                    || case  when when_value_null is not null then chr(44) || when_value_null || chr(41)  end || '"' || v1 || '"';
    end loop;
    close c1;
    tempstr:=sqlstr || ' from ' || case when rowcount_str is not null then '( '||rowsqlstr||' )' else tabname end||' group by ' || group_col || case when roworder is not null then ' order by ' || roworder end;
    --DBMS_OUTPUT.put_line(length(tempstr));--查看是否超过4000个字符
    open cur for tempstr;
  end Sp_Row_To_Col;