526互联

Oracle行列操作--合并行与按字段拆分

发布时间 2023-08-23 18:39:44作者: jingkunliu

1、在实际工作中遇到根据某一字段将多行合并成一行的情况，我们下面以选修课的例子进行说明：

-- create table
create table XXK
(
  id    NUMBER,
  rymc  NVARCHAR2(50),
  xxkmc NVARCHAR2(50)
)
---insert test data
insert into XXK values(1,'小明','编程');
insert into XXK values(2,'小明','绘画');
insert into XXK values(3,'小明','音乐');
insert into XXK values(4,'小红','绘画');
insert into XXK values(5,'小红','音乐');
image
如果我们要实现将所有的人员显示为一行，选修的不同可成通过逗号连接呢（下图效果）？
image
这个可以使用Oracle的LISTAGG函数，其具体语法如下
LISTAGG(column, delimiter) WITHIN GROUP (ORDER BY column)
其中：
column为需要合并的列的名称
delimiter为分割符号
那么实现的语句我们可以写成：
SELECT T.RYMC, LISTAGG(TO_CHAR(T.XXKMC), ',') WITHIN GROUP(ORDER BY XXKMC) AS XXKLIST
  FROM XXK t
 GROUP BY T.RYMC
特别注意：
我在具体使用过程中，有时候往往写的语句没有问题，就是不返回结果或者返回结果有乱码等情况，这是因为字段的数据类型导致的，进行一下转换即可，就如上面的SQL，其通过TO_CHAR(T.XXKMC)对选修课名称进行了类型转换。
2、如果我要实现相反的效果呢？先制作测试数据。
-- create table
create table XXK2
(
  id    NUMBER,
  rymc  NVARCHAR2(50),
  xxkmc NVARCHAR2(50)
)
---insert test data
insert into XXK2 values(1,'小明','编程,绘画,音乐');
insert into XXK2 values(2,'小红','绘画,音乐');
这里可以使用regexp_substr函数来实现：
regexp_substr(str, pattern [, position [, occurrence [, match_param]]])
其中：
str是需要进行处理的字符串列
pattern是正则表达式
position其实位置，从第几个字符开始匹配
occurrence获取第几个分割出来的组
match_param模式，i不区分大小写，c为区分大小写，默认是c
具体语句如下：
SELECT T.RYMC, REGEXP_SUBSTR(T.XXKMC, '[^,]+', 1, Level) AS XXK_CF
  FROM XXK2 T
CONNECT BY Level<= REGEXP_COUNT (T.XXKMC, ',' ) +1  and t.RYMC = prior t.RYMC  and prior dbms_random.value is not null
注意：
在本例中，level并不是固定不变的，要根据实际选修的课程数来确定，其中REGEXP_COUNT就是来做这个工作的。
需要通过人员进行限定同时处理空的情况t.RYMC = prior t.RYMC  and prior dbms_random.value is not null，否则会出现错乱的情况，如下所示：
image