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

一、wm_concat函数

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

测试用表test_org_bak

1 select * from test_org_bak;

1、:行转列(默认逗号隔开)

1 select wm_concat(level_four_name) name from test_org_bak;

2、:更换分隔符

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

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

1 select level_three_name,wm_concat(level_four_name) name from test_org_bak group bylevel_three_name;2 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()方法去列转行。或者自定义个方法去实现行转列。

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

结果同上1

1 selectlevel_three_name,2 listagg(level_four_name, ',') within group(order bylevel_four_name) level_four_name13 fromtest_org_bak4 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来让这个需求变简单

1 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