oracle多条数据合并成同一条
1、wmsys.wm_concat(字段名)
注意: A——字段名 必须是 字符类型,如果不是,需要用to_char转换
例如:
select wmsys.wm_concat(to_char((customer_name))) from A where application_no=BF-A170418000
B——如果想其它分隔符分割,可用replace 例如:
select wmsys.wm_concat(to_char((customer_name))) ,replace (wmsys.wm_concat(to_char((customer_name))),,,/)as co_name from A where application_no=BF-A170418000
XMLAGG(XMLPARSE(CONTENT 字段名 ||,WELLFORMED)).GETCLOBVAL()
select XMLAGG(XMLPARSE(CONTENT customer_name || 、 WELLFORMED)).GETCLOBVAL() from AFC_APPLICANT_INFO where application_no=BF-A170418000
注意: A——字段名 必须是 字符类型,如果不是,需要用to_char转换 B——最后多一个符号( 使用 RTRIM函数去掉最后多余符号。即RTRIM(XMLAGG(XMLPARSE(CONTENT to_char(字段A) || , WELLFORMED)).GETCLOBVAL(),,),但是RTRIM是有长度限制的) 例如:
select RTRIM(XMLAGG(XMLPARSE(CONTENT to_char(customer_name) || 、 WELLFORMED)).GETCLOBVAL(),、) from AFC_APPLICANT_INFO where application_no=BF-A170418000
可以实现 将多条 同一个application_no的数据 合并为一条
select application_no ,INDIVIDUAL_NAME,RTRIM(XMLAGG(XMLPARSE(CONTENT to_char(customer_name) || 、 WELLFORMED)).GETCLOBVAL(),、) as name from A where application_no=BF-A170418000 group by application_no,INDIVIDUAL_NAME
listagg(字段名,、) within group(ORDER BY 字段名)
select application_no ,INDIVIDUAL_NAME, listagg(a.customer_name,、) within group(ORDER BY a.customer_name) as names from a where application_no=BF-A170418000 group by application_no,INDIVIDUAL_NAME
下一篇:
各个版本的版本sqlserver下载地址