Oracle 行转列（不固定行数的行转列，动态）（转）

http://bbs.csdn.net/topics/330039676

 

SQLSERVER :行列转换例子：  http://www.cnblogs.com/gaizai/p/3753296.html#_labelFour

 

 

1. Oracle 11g之后新增了行列转换的函数 pivot 和 unpivot 大大简化了行列转换处理。

2. 在Oracle 10g及以前版本，一般是通过各种SQL进行行列转换，列入下面例子：

create or replace procedure P_row_to_col(tabname         in varchar2,
                                       group_col       in varchar2,
                                       column_col      in varchar2,
                                       value_col       in varchar2,
                                       Aggregate_func  in varchar2 default 'max',
                                       colorder        in varchar2 default null,
                                       roworder        in varchar2 default null,
                                       when_value_null in varchar2 default null,
                                       viewname        in varchar2 default 'v_tmp')
  Authid Current_User as sqlstr varchar2(2000) := 'create or replace view ' || viewname || ' as select ' || group_col || ' '; 
  c1 sys_refcursor; 
  v1 varchar2(100);
begin
open c1 for 'select distinct ' || column_col || ' from ' || tabname || case when colorder is
not null then ' order by ' || colorder
end; 
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; 
sqlstr := sqlstr || ' from ' || tabname || ' group by ' || group_col || case when roworder is
not null then ' order by ' || roworder
end; 
execute immediate sqlstr;
end P_row_to_col;

--测试数据 
create table rowtocol_test as 
select 2009 year,1 month,'AAA1' dept,50000 expenditure from dual 
union all select 2009,2,'AAA1',20000 from dual 
union all select 2009,2,'AAA1',30000 from dual 
union all select 2010,1,'AAA1',35000 from dual 
union all select 2009,2,'BBB2',40000 from dual 
union all select 2009,3,'BBB2',25000 from dual 
union all select 2010,2,'DDD3',60000 from dual 
union all select 2009,2,'DDD3',15000 from dual 
union all select 2009,2,'DDD3',10000 from dual; 

select * from rowtocol_test; 

--执行测试 

begin
p_row_to_col('rowtocol_test','year,month','dept','expenditure',Aggregate_func => 'sum',colorder => 'dept',roworder => '1,2',when_value_null => '0')
;
end;

select * from v_tmp;
================================================================================================================

 

例子二 三：

--测试数据 
create table t (XH varchar2(10), DDATE date, SXF int); 
insert into t select 1,sysdate,10 from dual 
union all select 1,sysdate+1,14 from dual 
union allselect 1,sysdate+2,23 from dual 
union allselect 2,sysdate,21 from dual 
union allselect 2,sysdate+1,24 from dual 
union allselect 3,sysdate,13 from dual 
union allselect 3,sysdate+1,22 from dual;
 -- 
 create or replace package sp_test istype ResultData is ref cursor; 
 procedure getRstData( rst out ResultData); end sp_test; 
 / 
 create or replace package body sp_test isprocedure getRstData( rst out ResultData) 
 is begin 
 declare cursor cur is select distinct (DDATE) from t; 
 tmp_ddate date; 
 str varchar2(4000); 
 beginstr:='select xh'; 
 open cur; 
 loop fetch cur into tmp_ddate; 
 exit when cur%notfound; 
 str:=str||',sum(decode(to_char(ddate,''yyyymmdd''),'||chr(39)||to_char(tmp_ddate,'yyyymmdd')||chr(39)||',sxf,0)) "'||to_char(tmp_ddate,'yyyymmdd')||'"'; 
 end loop; 
 str:=str||' from t group by xh'; 
 -- 
 dbms_output.put_line(str); 
 close cur; 
 open rst for str; end; 
 end;
  end sp_test; 
  / 
  --输出结果 
  1 10 14 
  23 2 21 
  24 0 3 
  13 22 0
  
  
  ========================
  例子三：
  ------------建表
  CREATE TABLE TEST(WL VARCHAR2(10),XYSL INTEGER,XYCK VARCHAR2(10),XCLCK VARCHAR2(10),XCLCKSL INTEGER,PC INTEGER);
  ------------ 第一部分测试数据
  INSERT INTO TEST VALUES('A1', 2, 'C1', 'C1' ,        20,         123); 
  INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' ,        30,         111); 
  INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' ,        20,         222); 
  INSERT INTO TEST VALUES('A1', 2, 'C1', 'C3' ,        10,         211); 
  INSERT INTO TEST VALUES('A2', 3, 'C4', 'C1' ,        40,         321); 
  INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' ,        50,         222); 
  INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' ,        60,         333); 
  INSERT INTO TEST VALUES('A2', 3, 'C4', 'C5' ,        70,         223);
  COMMIT;
  --------------------  动态生成结果表
  DECLARE  V_SQL VARCHAR2(2000);  
  CURSOR CURSOR_1 IS    SELECT DISTINCT T.XCLCK      FROM TEST T     ORDER BY XCLCK; 
  BEGIN  V_SQL := 'SELECT WL,XYSL,XYCK';  
  FOR V_XCLCK IN CURSOR_1  
    LOOP    V_SQL := V_SQL || ',' || 'SUM(DECODE(XCLCK,''' || V_XCLCK.XCLCK ||  ''',XCLCKSL,0)) AS ' || V_XCLCK.XCLCK;  
    END LOOP;  
    V_SQL := V_SQL || ' FROM TEST GROUP BY WL,XYSL,XYCK ORDER BY WL,XYSL,XYCK'; 
  --DBMS_OUTPUT.PUT_LINE(V_SQL);
  V_SQL := 'CREATE TABLE RESULT  AS '||  V_SQL;
  --DBMS_OUTPUT.PUT_LINE(V_SQL);
  EXECUTE IMMEDIATE V_SQL;
  END;
  --------------- 结果
  SELECT * FROM RESULT T;