oracle把多行合并成字符串,怎样将Oracle多行转换成字符串？

在做一些比较复杂的DB数据导出时，有时会要求“将不固定的多行数据组合成一个字符串返回”。

例子：ISV Portal中就遇到了类似的情况，要求对于每一个APP，返回其所属的所有类目名称，类目名称之间用[,]隔开。

本文就用此例子来介绍。在具体陈述实现方案之前，我们先介绍下我们即将操作的表结构：

SQL> desc app_category_link;

Name Type Nullable Default Comments

APP_CATEGORY_LINK_ID VARCHAR2(20) 主关键

APP_ID VARCHAR2(20) 应用ID

APP_CATEGORY_ID VARCHAR2(20) 应用类别ID

其中字段APP_ID和APP_CATEGORY_ID是一对多关系；

对于该类型的问题，总结一下大致有如下几种常见方案：

方案1：sys_connect_by_path + start with 。

。。 connect by 。。。 prior + 分析函数

从上面的这个公式中我们可以看出，该方案主要是通过分析函数和父子级联查询来完成，一般是一条SQL搞定，比较省事。首先来看几个具体的实现SQL。

具体实现1：

SELECT app_id,

ltrim(max(sys_connect_by_path(app_category_id, ',')), ',') categ_ids

FROM (SELECT app_id,

app_category_id,

app_category_id || '|' || rn rchild,

app_category_id || '|' || (rn - 1) rfather

FROM (SELECT app_id,

app_category_id,

row_number() over(PARTITION BY app_id ORDER BY app_category_id) rn

FROM app_category_link))

START WITH rfather LIKE '%|0'

CONNECT BY PRIOR rchild = rfather

GROUP BY app_id;

具体实现2：

select app_id,

ltrim(max(sys_connect_by_path(app_category_id, ',')), ',') categ_ids

from (select t。

app_id,

t。app_category_id,

min(t。app_category_id) over(partition by app_id) categ_min,

(row_number() over(order by app_id, app_category_id)) +

(dense_rank() over(order by app_id)) numid

from app_category_link t)

start with app_category_id = categ_min

connect by numid - 1 = prior numid

group by app_id;

具体实现3：

select app_id,

ltrim(max(sys_connect_by_path(app_category_id, ',')), ',') categ_ids

from (select t。

app_id,

t。app_category_id,

(row_number()

over(partition by app_id order by app_category_id)) numid

from app_category_link t)

start with numid = 1

connect by numid - 1 = prior numid

and app_id = prior app_id

group by app_id;

具体实现4：

select app_id,

ltrim(sys_connect_by_path(app_category_id, ','), ',') categ_ids

from (select t。

app_id,

t。

app_category_id,

(row_number()

over(partition by app_id order by app_category_id)) numid

from app_category_link t)

WHERE connect_by_isleaf = 1

start with numid = 1

connect by numid - 1 = prior numid

and app_id = prior app_id;

请注意看4种实现方式的区别，下面分别介绍下这4种实现方式的具体思路；

第1种实现采用了1个分析函数、2次子查询、一个like、以及父子级联查询字段值连接；可以猜测下性能肯定不咋的，2次子查询本来已经很耗时了，对查询出来的结果集还要用like匹配，速度就更慢了，此法可以查询到我们需要的具体数据，但是效率很低，不可取；他的实现思路是利用待查询字段值与各APP下面各类目ID的序列值进行组合，并作为父子关系级联的依据；

第2种实现采用了3个分析函数、1次全表扫描、以及父子级联字段值连接；和第1种实现比较而言的话效率会高不少；他的实现思路是利用各APP对应的最小类目ID作为父子级联的开始点，而父子级联的依据是row_number()+dense_rank()，这样做主要是为了避免无限循环；

第

3、4两种实现思路基本上是一样的，都是1个分析函数、1次全表扫描、以及父子级联字段值连接；从代码长度来说，比前2种实现方式简洁了不少，思路也清晰了很多，直接利用各APP对应类目ID的序列值作为父子级联的开始点和连接依据；但仔细看看两者的SQL，会发现第3这种方式用到了group by子句，而第4种实现却没有用到，而是在where子句中添加了connect_by_isleaf = 1 的查询条件；从性能上来看，应该是第4种实现方式更高，但他只能在10g及其以后的版本中才能使用，connect_by_isleaf 字段是10g中新提供的一个伪列，他可以用来判断该条记录是否是树形记录的叶节点，不过还在用9i版本的可能就有些可惜了；

综合以上分析，对4种实现方案，个人推荐使用第

3、4两种实现方式，具体哪种可以看所用oracle的版本而定，简而言之，这种实现方式优雅、简洁、高效；。

全部