ORACLE SQL regexp_substr 单列多行转单行单列合并 与 LISTAGG 将多行结果合并成一行

本文介绍了如何使用ORACLE SQL的REGEXP_SUBSTR函数结合ConnectBy进行单列多行数据的动态拆分,并利用LISTAGG函数将拆分后的结果合并为单行。涉及正则表达式应用和聚合函数技巧。
摘要由CSDN通过智能技术生成

**

ORACLE sql 单列多行转单行单列合并

引用:https://www.cnblogs.com/su-root/p/15008584.html
1、regexp_substr函数,通过正则来拆分字符串,函数用法为:(必须是oracle 10g+的版本才支持)
REGEXP_SUBSTR函数格式如下:

function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
__srcstr :需要进行正则处理的字符串
__pattern :进行匹配的正则表达式
__position :起始位置,从第几个字符开始正则表达式匹配(默认为1)
__occurrence :获取第几个分割出来的组(分割后最初的字符串会按分割的顺序排列成组),默认为1
__modifier :模式('i’不区分大小写进行检索;‘c’区分大小写进行检索。默认为’c’。)针对的是正则表达式里字符大小写的匹配


在这里插入图片描述

~~

此函数只能每次取一个字符串出来,有点鸡肋,字符串中逗号的数量是不确定的,如果有2个逗号,需要提取的字段就是3个。为了确定有多少个需要提取的字段,需要用到connect by命令实现动态参数构造连续的值,通过原字符串长度和被替换后字符串长度相减,可以得到原字符串中的逗号数量,加1后得到需要提取的匹配字段数量。

~~

**select bs from cs1_0 where slid='201804100038'
--正则分割后的第一个值
SELECT REGEXP_SUBSTR((select bs from cs1_0 where slid='201804100038'),'[^,]+',1,1,'i') as 分割后结果 FROM DUAL;
--获取一个多个数值的列,从而能够让结果以多行的形式展示出来 
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=5;
--将上面REGEXP_SUBSTR的occurrence(标识第几个匹配组)实现动态参数,使用 connect by组合起来
SELECT REGEXP_SUBSTR((select bs from cs1_0 where slid='201804100038'),'[^,]+',1,LEVEL,'i') as 分割后结果 FROM DUAL CONNECT BY LEVEL <=5;
--优化一下(动态获匹配组标识行数)
select regexp_substr((select bs from cs1_0 where slid='201804100038'),'[^,]+',1,LEVEL,'i') as 分割后结果 from dual 
connect by level <= length((select bs from cs1_0 where slid='201804100038'))-length(regexp_replace((select bs from cs1_0 where slid='201804100038'),',',''))+1;

2、oracle 聚合函数 LISTAGG ,将多行结果合并成一行

引用:https://blog.csdn.net/sinat_35626559/article/details/72621695

LISTAGG(列名,’ 分割符号’)

oracle 11g 以上的版本才有的一个将指定列名的多行查询结果,用 指定的分割符号 合并成一行显示:

例如:表原始数据:
在这里插入图片描述
需求:将 mb1_Transport_License_list 表中的数据,根据 transportation_license_id 数据进行分组,对 Item_Category_Name 列的数据进行 去重合并
使用聚合函数 LISTAGG 解决

SELECT transportation_license_id,
     LISTAGG( to_char(Item_Category_Name), ',') WITHIN GROUP(ORDER BY Item_Category_Name) AS employees
      FROM ( select distinct transportation_license_id, item_category_name from mb1_Transport_License_list  ) group by transportation_license_id

SQL解析:

select distinct transportation_license_id, item_category_name from mb1_Transport_Lincense_list ; – 对需要做合并处理的数据源数据进行去重处理,如果实际要求不需要去重处理,这里可以直接改为 表名,(例如: from mb1_Transport_Lincense_list)进行查询

LISTAGG( to_char(Item_Category_Name), ‘,’) WITHIN GROUP(ORDER BY Item_Category_Name) – 将 Item_Category_Name 列的内容以", "进行分割合并、排序;

to_char(Item_Category_Name) – to_char(列名) 解决使用聚合函数 LISTAGG 进行查询后,对查询结果乱码问题进行转码处理;

运行后的结果:

在这里插入图片描述

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值