oracle的行列转换函数，wm_concat()方法与的排序问题，Oracle的 listagg 函数

 wm_concat聚合无法直接排序聚合的问题和oracle 11g的新函数listagg。

 

Oracle中的 wm_concat()  函数的使用

功能是：实现行转列功能，即将查询出的某一列值使用逗号进行隔开拼接，成为一条数据。

如：

 

select u_id,   to_char(wmsys.wm_concat(name )  )   as name   from thomas   group by charge_code 

 

 
select u_id,   to_char(wmsys.wm_concat(distinct name )  )   as name   from thomas   group by charge_code 

 

 

 

上面需要注意的点事用to_char()转一下，不然返回的就是clob类型的结果，不够直观

select u_id, wmsys.wm_concat(name || '(' || num || '斤)' )  as name    from shopping   group by u_id   

结果显示为

 

 

 上面charge_code为2的时，有两个梨子，如何实现 梨子（11）呢？

 

 

=============================================================================

2、需求：

关于wm_concat聚合无法直接排序聚合的问题和oracle 11g的新函数listagg。

3、准备数据：

 

 

需要的结果是这样，对gradenm列按gradeno列进行排序合并

 

 

4、实现
（1）wm_concat() 聚合，代码及结果如下：

 

这个时候很明显看到，wm_concat默认是没有办法给gradenm排序的，当然你可以把from table的部分先查出进行排序，

再进行wm_concat聚合，我只能告诉你这样并没有什么卵用。

而且wm_concat默认是用逗号进行字符串拼接，如果需求是别的分隔符，还得用replace去替换一下。

 

（2）尝试用wm_concat()over(partition by xx order by xx) 分析函数改进，代码及结果如下：

 

 

 

 

通过分析函数的patition进行分区分组，然后排序是order by gradeno，这样，嗯。。看结果。然后我们外面套上一层select 查询这个结果，按schooltype分组，取grade最长的，就可以查到想要的结果了。代码如下：

select schooltype, max(grade) grade
from (select schooltype, gradeno, wm_concat(gradenm) over(partition by schooltype order by gradeno) grade
from schoolgrade a)
group by schooltype;

 

 

 

（3）当然，不可能就这么结束了，今天的主角其实是listagg函数，

Oracle Database 11g开始提供的一个聚合函数，配合分组实现上面的需求，代码和结果如下：

--listagg()within group(order by xx) 

select schooltype, listagg(gradenm, '、') within group(order by gradeno) gradenm
from schoolgrade
group by schooltype;

 

 

 

listagg() 可接收两个参数，聚合列 和 分隔符，不写分隔符参数即无分隔符直接拼接。

within group(order by xx)里面的就是聚合列拼接顺序的排序，用法跟普通排序order by 一样。

group by，分组，按组统计。有用到分组的才需要。

****************************************

补充：wm_concat是在wmsys用户下的一个函数，是oracle的一个非公开的函数，而且，wm_concat在前面几个版本中的返回值数据类型也不相同，在新版本12c中更是直接被抛弃了。

所以，日常开发中并不建议使用wm_concat。强行建议使用listagg。要么就自己写个函数用。

 

 

 

原文链接：https://blog.csdn.net/Huay_Li/article/details/81257655