环境(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行。
图片效果: