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

核心提示： step 3 利用sys_connect_by_path生成结果集：SELECT country,sys_connect_by_path(city,',') cityFROM(SELECT country,city,country||rn rchild,country||(

step 3 利用sys_connect_by_path生成结果集：

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'
日本 ,大阪
日本 ,大阪,东京
中国 ,上海
中国 ,上海,台北
中国 ,上海,台北,香港

step 4 最终步骤，筛选结果集合：

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
-------------------- -------
中国　　　　　　　　 上海,台北,香港
日本　　　　　　　　 大阪,东京

5.自定义聚合函数：

灵活性★★★★★ 性能★★★★★ 难度 ★★★★★

最后一个方法是我认为“王道”的方法，自定义聚合函数。就如何我在本开始说的，为啥oracle没有这种聚合函数呢？我也不知道，但Oracle提供了聚合函数的API可以让我方便的自己定义聚合函数。

下面给出一个简单的例子：

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
)

6.待发掘：

总结，合并字符串还有更多的方法希望大家能发掘，本文的目的主要是抛砖引玉，如果有新的发现我会继续更新方法。需要注意的问题是，本文采用varchar2为例子，所以长度有限制，Oracle的版本对方法的实现也影响。