Oracle多行转单行

Oracle多行转单行

Oracle多行转单行

create or replace type vars is table of varchar2(10000);
create or replace function sumvarc (p_in in vars) return clob
is
  v_out clob;
begin
  for i in 1..p_in.count loop
    v_out := v_out || p_in(i);
  end loop;
  return v_out;
end;
--用法

select user_name,
         sumvar(cast(multiset(select ','||function_name
                                from user_funciotn
                               where a.user_name=user_name
                               order by function_name) as vars
                    )
               ) as aggr
  from user_funciotn