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 objectCREATE or replace TYPE unit_type AS OBJECT       ( unit1     VARCHAR2(50)        ,unit2     VARCHAR2(50)        ,unit3     VARCHAR2(50)        ,unit4     VARCHAR2(50)       );---create table object by type ObjectCREATE TYPE unit AS TABLE OF unit_type;---create tablecreate table t_unit (unitname varchar2(50));---insert into databegininsert 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 dataselect * from t_unit;---create pipe function ,  important stepcreate 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 effectSQL> select * from table(autolinefeed);UNIT1      UNIT2      UNIT3      UNIT4---------- ---------- ---------- ----------a          b          c          de          f           g          hi           j           k          lm         n          o          pr           s          t          uv          w         x          yz          q          1          23          4已选择8行。

?

图片效果：

?