oracle 行列转换函数之WM_CONCAT和LISTAGG的使用（一）

一、wm_concat函数

wm_concat能够实现同样的功能，但是有时在11g中使用需要用to_char()进行转换，否则会出现不兼容现象(WMSYS.WM_CONCAT: 依赖WMSYS 用户，不同oracle环境时可能用不了，返回类型为CLOB，可用substr截取长度后to_char转化为字符类型)。

测试用表test_org_bak

select * from test_org_bak;

1、:行转列（默认逗号隔开）

select wm_concat(level_four_name) name from test_org_bak;

2、:更换分隔符

select replace(wm_concat(level_four_name),',','|') from test_org_bak;

3、:按照事业部分组合并大区

select level_three_name,wm_concat(level_four_name) name from test_org_bak group by level_three_name;
select level_three_name,wm_concat(distinct level_four_name) name from test_org_bak group by level_three_name;--可去重

二、listagg函数

注意:listagg函数的第一个参数是需要显示的字段，也就是log_name；第二个参数是数值之间的分隔符；同时还需要进行排序和分组within group (order by name)

一般推荐使用LISTAGG()方法去列转行。或者自定义个方法去实现行转列。

select listagg(level_four_name, ',') within group(order by level_four_name) level_four_name1 from test_org_bak;

结果同上1

select level_three_name,
       listagg(level_four_name, ',') within group(order by level_four_name) level_four_name1
  from test_org_bak
 group by level_three_name;

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

还需注意：LISTAGG:11g2才提供的函数，不支持distinct,拼接长度不能大于4000,函数返回为varchar2类型，最大长度为4000;

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

select 'create or replace view as select '|| wm_concat(column_name) || ' from test'from user_tab_columns where table_name='TEST';

)

（注：此为学习记录笔记，仅供参考若有问题请指正，后续补充......）

参考：https://blog.csdn.net/oJerryZuo/article/details/53927057