📣1. 查询要合并的数据
select pk_org,name from bd_defdoc where pk_defdoclist = '1001A11000000001O6IB'
📣2. 直接合并
--非大文本
select pk_org, listagg(name, ',') within group(order by name) as names
from bd_defdoc
where pk_defdoclist = '1001A11000000001O6IB'
group by pk_org
📣3. 合并成大文本(不去除后面的都逗号)
在Oracle 查询数据时有时候会需要用到 列转行,把多行拼接在一起,一般我习惯使用listagg()函数,但是如果拼接的记录太多会报错。listagg 拼接返回的类型为varchar ,最大长度为4000,当长度过长时会报错,
ora-01489 result of String concatenation is too long
此时的解决思路就是 将返回的varchar类型 转换为clob类型,这样长度就可以满足返回值的要求了。
使用xmlagg 函数 ,使用方式和listagg类似,但是有一些地方不太一样。
xmlparse 函数可以理解为是将里面的数据组织成xml文档,然后 利用xmlagg方法将这些xml实例进行拼接。需要注意的是xmlparse 中content 后面跟的是每行数据需要拼接的部分
,将每行数据都连接了一个“,”逗号,所以最后一行的最后也会有一个逗号,所以需要去掉最后面的逗号,listagg 是在多个元素之间的连接部分拼接符号,而 xmlagg只是将多个xml实例进行拼接,所以会多着一个“,”。
--大文本不去逗号
select pk_org, xmlagg(xmlparse(content name || ',' wellformed) order by name).getclobval() as names
from bd_defdoc
where pk_defdoclist = '1001A11000000001O6IB'
group by pk_org
📣4. 合并成大文本(去除后面的都逗号)
看到网上也有使用xmlelement 来返回XMLType的实例,因此一起记录下来,这个函数感觉和xmlparse函数类似,也是拼接每一个xml实例的值,因为设置的每一行最后拼接“,”逗号,所以也需要使用rtrim 来除去最后的符号,也可以在java里查出来再进行处理。
--大文本去逗号
select pk_org,rtrim(xmlagg(xmlelement(e, name, ',').extract('//text()') order by name).getclobval(),',') as names
from bd_defdoc
where pk_defdoclist = '1001A11000000001O6IB'
group by pk_org