在Oracle 查询数据时有时候会需要用到 列转行, 把多行拼接在一起,一般我习惯使用listagg()函数,但是如果拼接的记录太多会报错.
listagg 拼接返回的类型为varchar ,最大长度为4000,当长度过长时会报错, ora-01489 result of String concatenation is too long

此时的解决思路就是 将返回的varchar类型 转换为clob类型,这样长度就可以满足返回值的要求了.

1.使用xmlagg 函数 ,使用方式和listagg类似,但是有一些地方不太一样

假设存在 表user(id, name,dept ) 现在要查出每个部门的用户id和姓名.

-- 语法
xmlagg(xmlparse(content 拼接字段 || ','wellformed) order by 排序字段).getclobval()

--实例
select dept, xmlagg(xmlparse(content id || ',' wellformed) order by id).getclobval() ids,xmlagg(xmlparse(content name|| ',' wellformed) order by id).getclobval() names
from user group by dept

xmlparse 函数可以理解为是将里面的数据组织成xml文档,然后 利用xmlagg方法将这些xml实例进行拼接. 需要注意的是xmlparse 中content 后面跟的是每行数据需要拼接的部分 , 将每行数据都连接了一个,逗号,所以最后一行的最后也会有一个逗号,所以需要去掉最后面的逗号,listagg 是在多个元素之间的连接部分拼接符号,而 xmlagg只是将多个xml实例进行拼接,所以会多着一个, 。

改善版

select dept, 
rtrim(xmlagg(xmlparse(content id || ',' wellformed) order by id).getclobval(),',' ) ids,  
rtrim( xmlagg(xmlparse(content name|| ',' wellformed) order by id).getclobval(),',') names
from user group by dept

看到网上也有使用xmlelement 来返回XMLType的实例,因此一起记录下来,这个函数感觉和xmlparse函数类似,也是拼接每一个xml实例的值,因为设置的每一行最后拼接, 逗号,所以也需要使用rtrim 来除去最后的符号,也可以在java里查出来再进行处理.

 --语法
 xmlagg( xmlelement(e,拼接字段,',').extract('//text()') order by 排序字段 ).getclobval()

-- 不去逗号版
select dept,
xmlagg(xmlelement(e,id,',').extract('//tetx()') order by id).getclobval() ids,
xmlagg(xmlelement(e,name,',').extract('//text()') order by id).getclobval() names
from user group by dept

-- 去掉末尾的连接符号版
select dept,
rtrim( xmlagg(xmlelement(e,id,',').extract('//tetx()') order by id).getclobval(),',') ids,
rtrim(xmlagg(xmlelement(e,name,',').extract('//text()') order by id).getclobval(),',') names
from user group by dept

oracle xml处理函数简介

1. EXTRACT（XMLType_instance，Xpath_string）
   该函数用于返回XML节点路径下的相应内容
   示例：

SELECT extract(value(a),'/root/main') data FROM xmltable a ;

2. SYS_XMLAGG（expr[，fmt]）
   该函数用于汇总所有XML文档，并生成一个XML文档。

示例:

SELECT SYS_XMLAGG(SYS_XMLGEN(a.order_content)) xml_content
from doc_clinic_order a, clinics_item b, med_frequencydict c
where a.patient_id = 'bd4b425e-a409-4b28-890d-d1d668fcf725'
and a.parentid = '0'
and a.route = b.item_id(+)
and a.frequency = c.frequency_id(+)

3. XMLAGG（XMLType_instance[ORDER BY sort_list])
   该函数用于汇总多个XML块，并生成XML文档。
   示例:

select xmlagg(xmlelement("row",xmlforest(a.frequency_id as "频次ID", a.frequency_name as "频次名称")))[]
from med_frequencydict a

4. XMLELEMENT(identifier[,xml_attribute_clause][,value_expr])
   该函数用于返回XMLType的实例。其中参数identifier用于指定元素名，参数xml_attribute_clause用于指定元素属性子句，参数value_expr用于指定元素值。
   示例:

SELECT xmlelement("row",xmlcolattval(a.frequency_code AS "编码",a.frequency_name as "名称")) xml
from med_frequencydict a
where a.frequency_code = 'BID'

xml处理函数参考自https://www.jb51.net/article/84876.htm 脚本之家，想了解更多xml处理函数可以去该网址了解详情