oracle 将多行合并成一行

时间:2021-07-27 10:30:43

一、概述

       参考如下表数据:

用户ID 用户名 角色
001 admin 管理员
002 betty 教师
001 admin 教师
003 cole 学生

         可以看到用户admin有两个角色-----管理员和教师(也就是用户和角色是多对多的关系),我们现在的需求是,将这两个合并在一块,由两行数据变成一行数据(也就是在形式上将多对多的关系转成多对一的关系),如下图所示:

ID 用户名 角色
001 admin 管理员,教师
002 betty 教师
003 cole 学生

如何做到呢?我们可以通过wm_concat函数来达到我们的目的:

SELECT other_colums, wmsys.wm_concat (join_colum) as join_result

                FROM table_name

                             GROUP BY other_colums

--将join_colum替换成待合并的列

--将other_colums替换成剩余的列

--将table_name替换成表名

例子:

1.假如有如下视图:

oracle 将多行合并成一行

其中用户bbb既是管理员又是老师,查询语句如下:

select userid,username,phone ,wmsys.wm_concat(rolename) as rolename from test_user group by userid,username,phone;

结果如下:

oracle 将多行合并成一行

用户bbb的rolename的内容如下:

oracle 将多行合并成一行

拼接后的类型是CLOB,当然如果要拼接的内容不大,可以在拼接后使用to_char()转一下。如果CLOB的内容是乱码的话,也可以在拼接前对要拼接的内容使用to_char()转换一下:

select userid,username,phone ,to_char(wmsys.wm_concat(to_char(rolename))) as rolename from test_user group by userid,username,phone;

结果如下:

oracle 将多行合并成一行

2.拼接不仅限于varchar2类型的rolename,对于number类型的phone同样可以做到:

select userid,username,to_char(wmsys.wm_concat(phone)) as phone ,to_char(wmsys.wm_concat(rolename)) as rolename from test_user group by userid,username;

结果如下:

oracle 将多行合并成一行

3.如果分隔符不想要逗号“,”而是分号呢?很简单,可以使用replace函数替换:

select userid,username,replace(to_char(wmsys.wm_concat(phone)),',',';' )as phone ,to_char(wmsys.wm_concat(rolename)) as rolename from test_user group by userid,username;

结果如下:

oracle 将多行合并成一行


4.oracle wm_concat函数的弱点

 
(1) oracle wm_concat函数并不是万能的,它要求除了拼接的列不同之外,其它的列必须完全一致。我们现在考虑roleid:

   select userid,username,roleid,to_char(wmsys.wm_concat(rolename)) as rolename from test_user group by userid,username,roleid;

结果如下:

oracle 将多行合并成一行

我们可以发现并没达到预期的效果,如果去掉group by中的roleid,则会报错:“不是group by 表达式”,因为select 的字段必须出现在group by中才行。

所以凡事对于不同的列,要么不查询,要么同样要进行拼接,例如:

select userid,username,to_char(wmsys.wm_concat(roleid)) as roleid,to_char(wmsys.wm_concat(rolename)) as rolename from test_user group by userid,username;

结果如下:

oracle 将多行合并成一行

(2)对于含有CLOB等大字段类型,oracle wm_concat函数无能为力,原因是LOB字段是不能进行group by的,会报错:“ ORA-00932 :数据类型不一致:应为-,但却获得CLOB”。所以这样的字段是不能放入左边的select里面的,但如果CLOB字段的内容不大,在varchar2的范围内,就可以采取变通的方法,将clob转成varchar2类型:

select userid,username,to_char(wmsys.wm_concat(rolename)) as rolename,to_char(comments) as st  from test_user group by userid,username,to_char(comments);

结果如下:

oracle 将多行合并成一行


小结:

         网上有许多关于多行转一行的方法,不过很多是要自己写存储过程,函数的,显得麻烦。使用wm_concat函数,一个sql语句就可以搞定,方便,快捷。

附:使用oracle wm_concat函数创建含有大量列的视图

       网上有个利用oracle wm_concat函数创建含有大量列的视图的方法,在此也一并记录下来。

       含有大量列的视图类似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多个字段,要是靠手工写太麻烦了,有没有什么简便的方法? 当然有了,看我如果应用wm_concat来让这个需求变简单:


SQL> select 'create or replace view as select '|| wm_concat(column_name) || ' from DEPT'from user_tab_columns where table_name='DEPT';
--将两个地方的表名DEPT改成你的表就可以了,当然创建视图不可能只查询一张表,这里仅仅是一个例子,将所有的列都列出来,剩下就方便多了。