Oracle多行记录字符串综合操作几种方法

SQL> SELECT country,city,country||
rn rchild,country||(rn-1) rfather
  2    FROM
  3    (SELECT  test.country ,
test.city,row_number() over 
(PARTITION BY test.country ORDER 
BY test.city) rn
  4    FROM test)
  5  /
 日本 大阪 日本1 日本0
 日本 东京 日本2 日本1
 中国 上海 中国1 中国0
 中国 台北 中国2 中国1
       中国 香港 中国3 中国2

SELECT country,sys_connect_by_path(city,',') city
 FROM 
(SELECT country,city,country||
rn rchild,country||(rn-1) rfather
  FROM 
  (SELECT  test.country ,test.city,row_number() 
over (PARTITION BY test.country ORDER BY 
test.city) rn 
  FROM test))
CONNECT BY PRIOR rchild=rfather START WITH rfather LIKE '%0'
 日本 ,大阪
 日本 ,大阪,东京
 中国 ,上海
 中国 ,上海,台北
       中国 ,上海,台北,香港

SQL> SELECT country,max(substr(city,2)) city
  2    FROM
  3  (SELECT country,sys_connect_by_path(city,',') city
  4   FROM
  5  (SELECT country,city,country||rn rchild,country||
(rn-1) rfather
  6    FROM
  7    (SELECT  test.country ,test.city,row_number() 
over (PARTITION BY test.country ORDER 
BY test.city) rn
  8    FROM test))
  9  CONNECT BY PRIOR rchild=rfather START WITH rfather LIKE '%0')
 10   GROUP BY country;
COUNTRY              CITY
-------------------- -------
中国                 上海,台北,香港
      日本                 大阪,东京

SQL> SELECT t.country,strcat(t.city) 
FROM test t GROUP BY t.country;
COUNTRY              STRCAT(T.CITY)
-------------------- ------------------
日本                 东京,大阪
中国                 台北,香港,上海
简单吧，和官方的函数一样的便捷高效。
函数：
CREATE OR REPLACE FUNCTION strcat(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING strcat_type;
TYPE:
create or replace type strcat_type as object (
    cat_string varchar2(4000),
    static function ODCIAggregateInitialize
(cs_ctx In Out strcat_type) return number,
    member function ODCIAggregateIterate
(self In Out strcat_type,value in varchar2) return 
number,
    member function ODCIAggregateMerge
(self In Out strcat_type,ctx2 In Out strcat_type) 
return number,
    member function ODCIAggregateTerminate
(self In Out strcat_type,returnValue Out 
varchar2,flags in number) return number
      )