自己搞了函数行专列还是可以拼接

今天有个需求，要求把一个表里的根据一列（例如id）分组并且将每行的任意几列的数据挨着拼装在一起中间加特殊字符，
然后分组之后每个id对应的几行合成一条记录，例如：
SQL> select * from test;

P_ID NAME                 VALUE
———- ——————– ——————–
1 AAA                  aaa
2 BBB                  bbb
1 ABC                  abc
3 CCC                  ccc
2 DDD                  ddd

SQL> SELECT P_ID, Mystrcat_n(NAME||chr(01)||value) NAME FROM TEST GROUP BY P_ID;

P_ID
———-
NAME
——————————————————————————–
1

AAA
aaa
ABC
abc

2

BBB
bbb
DDD
ddd

3

CCC
ccc

寻求Sky.Jian（简朝阳）帮助，给我了一个他自己搞的一个函数，结果不满足我的要求，
在他的基础上修改一下，可以满足以上要求，中间是用chr(01)来分隔的，函数体如下：
（执行此函数到底消耗多少资源没有测试过）

create or replace type Mystrcat_new as object
(
str varchar2(4000),

static function ODCIAggregateInitialize(sctx IN OUT Mystrcat_new) return number,
member function ODCIAggregateIterate(self IN OUT Mystrcat_new,value IN varchar2) return number,
member function ODCIAggregateTerminate(self IN Mystrcat_new,returnValue OUT varchar2, flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT Mystrcat_new,ctx2 IN Mystrcat_new) return number
)
/

create or replace type body Mystrcat_new is
static function ODCIAggregateInitialize(sctx IN OUT Mystrcat_new)
return number is
begin
sctx := Mystrcat_new(null);
return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT Mystrcat_new, value IN varchar2)
return number is
begin
self.str := self.str||chr(01)||value;
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN Mystrcat_new, returnValue OUT varchar2, flags IN number) return number is
begin
returnValue := self.str;
return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT Mystrcat_new, ctx2 IN Mystrcat_new) return number is
begin
null;
return ODCIConst.Success;
end;
end;
/

CREATE OR REPLACE FUNCTION Mystrcat_n (input varchar2) RETURN varchar2
AGGREGATE USING Mystrcat_new;
/

SELECT P_ID, Mystrcat_n(NAME||chr(01)||value) NAME FROM TEST GROUP BY P_ID;