oracleÁÐתÐз½·¨×ܽá

·½·¨Ò»£º
----------------------------------------------------------------
---Muti-row to line(col2row)
----------------------------------------------------------------
create or replace type str_tab is table of varchar2(20);
/
grant all on str_tab to public;
create public synonym str_tab for str_tab;
create or replace function col2row(pv in str_tab) return varchar2
is
  ls varchar2(4000);
begin
  for i in 1..pv.count loop
    ls := ls || pv(i);
  end loop;
  return ls;
end;
/
grant execute on col2row to public;
create public synonym col2row for col2row;
----------------------------------------------------------------
--multi column,convert one column base on another column, for example
----------------------------------------------------------------
create table t(id number,name varchar2(10));
insert into t values(1,'Joan');
insert into t values(1,'Jack');
insert into t values(1,'Tom');
insert into t values(2,'Rose');
insert into t values(2,'Jenny');
---------------------------------------------------------------
SQL(c3dev)>select * from t;
        ID NAME
---------- ----------
         1 Joan
         1 Jack
         1 Tom
         2 Rose
         2 Jenny
---------------------------
--column to row
---------------------------
SQL(c3dev)>column names format a80;
SQL(c3dev)>set line 120
SQL(c3dev)>select t0.id,
  2     col2row(cast(multiset(select name from t where t.id = t0.id) as str_tab)) names
  3  from (select distinct id from t) t0;
        ID NAMES
---------- --------------------------------------------------------------------------------
    &nbs