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

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,'小红','音乐');

如果我们要实现将所有的人员显示为一行，选修的不同可成通过逗号连接呢（下图效果）？

这个可以使用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，否则会出现错乱的情况，如下所示：