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

来源：互联网发布：java高级工程师培训编辑：程序博客网时间：2024/05/16 14:31

 --行转列函数 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;

--执行查询SelectPKG_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;

0 0