Oracle 行自动转动态列一种实现

      环境(Oracle Database 10g Enterprise Edition Release 10.2.0.1.0)

     工作中碰到如此问题，业务特殊需求，如何实现动态的行转换成动态的列，源数据如，

11行数据：

col 

a

b

c

d

e

f

g

h

i

j

k

转成自定义的列表数据，如转成四列（变成了三行四列，四列可以做到动态设置）：

col1   col2  col3  col4

a        b      c       d

e        f       g       h

i         j        k

针对Oracle 10g特殊管道函数pipe row()，这里提供一种实现方式，以资学习记录。

按照顺序创建SQL脚本、模拟数据。

---create custom object
CREATE or replace TYPE unit_type AS OBJECT
       ( unit1     VARCHAR2(50)
        ,unit2     VARCHAR2(50)
        ,unit3     VARCHAR2(50)
        ,unit4     VARCHAR2(50)
       );

---create table object by type Object
CREATE TYPE unit AS TABLE OF unit_type;

---create table
create table t_unit (unitname varchar2(50));

---insert into data
begin
insert into t_unit (UNITNAME) values ('a');
insert into t_unit (UNITNAME) values ('b');
insert into t_unit (UNITNAME) values ('c');
insert into t_unit (UNITNAME) values ('d');
insert into t_unit (UNITNAME) values ('e');
insert into t_unit (UNITNAME) values ('f');
insert into t_unit (UNITNAME) values ('g');
insert into t_unit (UNITNAME) values ('h');
insert into t_unit (UNITNAME) values ('i');
insert into t_unit (UNITNAME) values ('j');
insert into t_unit (UNITNAME) values ('k');
insert into t_unit (UNITNAME) values ('l');
insert into t_unit (UNITNAME) values ('m');
insert into t_unit (UNITNAME) values ('n');
insert into t_unit (UNITNAME) values ('o');
insert into t_unit (UNITNAME) values ('p');
insert into t_unit (UNITNAME) values ('r');
insert into t_unit (UNITNAME) values ('s');
insert into t_unit (UNITNAME) values ('t');
insert into t_unit (UNITNAME) values ('u');
insert into t_unit (UNITNAME) values ('v');
insert into t_unit (UNITNAME) values ('w');
insert into t_unit (UNITNAME) values ('x');
insert into t_unit (UNITNAME) values ('y');
insert into t_unit (UNITNAME) values ('z');
insert into t_unit (UNITNAME) values ('q');
insert into t_unit (UNITNAME) values ('1');
insert into t_unit (UNITNAME) values ('2');
insert into t_unit (UNITNAME) values ('3');
insert into t_unit (UNITNAME) values ('4');
commit;
end;

---check data
select * from t_unit;

---create pipe function ,  important step
create or replace function autolinefeed     
 return unit PIPELINED    
 as  
  temp_str varchar2(32767):='';   
  temp_index number:=0;     
  temp_count number:=0;
  temp_sum number:=0;
  temp_unit1 varchar2(50);
  temp_unit2 varchar2(50);
  temp_unit3 varchar2(50);
  temp_unit4 varchar2(50);
 begin     
      select count(*) into temp_sum from t_unit;
      if temp_sum<=0 then
        pipe row(null);
        return;
      end if;
      for v_unit in (select * from t_unit) loop
        temp_count:=(temp_count+1);
        if (mod(temp_count,4)=1) then
          temp_unit1 :=v_unit.unitname;
        elsif (mod(temp_count,4)=2) then
          temp_unit2 :=v_unit.unitname;
        elsif (mod(temp_count,4)=3) then
          temp_unit3 :=v_unit.unitname;
        else
          temp_unit4 :=v_unit.unitname;
        end if;
        if (mod(temp_count,4)=0) then
          pipe row(unit_type(temp_unit1,temp_unit2,temp_unit3,temp_unit4));   
        else
          if (temp_sum=temp_count) then 
             if (mod(temp_count,4)=1) then
               pipe row(unit_type(temp_unit1,'','',''));
             elsif (mod(temp_count,4)=2) then
               pipe row(unit_type(temp_unit1,temp_unit2,'',''));
             elsif (mod(temp_count,4)=3) then
               pipe row(unit_type(temp_unit1,temp_unit2,temp_unit3,''));
             else
               pipe row(unit_type(temp_unit1,temp_unit2,temp_unit3,temp_unit4));  
             end if;
          end if;
        end if;
      end loop;
      exception   
       when others then  
       pipe row(null);    
 end;   
 
--data effect
SQL> select * from table(autolinefeed);

UNIT1      UNIT2      UNIT3      UNIT4
---------- ---------- ---------- ----------
a          b          c          d
e          f           g          h
i           j           k          l
m         n          o          p
r           s          t          u
v          w         x          y
z          q          1          2
3          4

已选择8行。

 

图片效果：