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行。?
图片效果：
?