sql的精妙解法

 

sql求解
表a
列 a1 a2
记录
1 a
1 b
2 x
2 y
2 z
用select能选成以下结果吗？
1 ab
2 xyz

使用pl/sql代码实现，但要求你组合后的长度不能超出oracle varchar2长度的限制。
下面是一个例子
create or replace type strings_table is table of varchar2(20);
/
create or replace function merge (pv in strings_table) return varchar2
is
ls varchar2(4000);
begin
for i in 1..pv.count loop
ls := ls || pv(i);
end loop;
return ls;
end;
/
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');

column names format a80;
select t0.id,merge(cast(multiset(select name from t where t.id = t0.id) as strings_table)) names
from (select distinct id from t) t0;

drop type strings_table;
drop function merge;
drop table t;