oracle 动态列

---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;    



select * from table(autolinefeed);