SQL
oracle

ListAgg関数とwmsys.wm_concat関数の違い

昔、US-OTNに投稿した

ListAgg関数とwmsys.wm_concat関数の違い

https://community.oracle.com/thread/1090158

を日本語に翻訳しつつ、全面リニューアルしてみました。

かつては、wmsys.wm_concat関数というマニュアルに載ってない関数があったのですが

11gR2XEや12cで消されてしまいました :scream:

ListAgg関数と似た関数だったのですが、

wmsys.wm_concat関数との違いをまとめてみます。


01 distinctオプションの有無

wmsys.wm_concat関数は、distinctオプションを指定できますが

ListAgg関数では不可です。

create table diffT(SortKey,Val) as

select 1,'aa' from dual union all
select 2,'bb' from dual union all
select 3,'aa' from dual union all
select 4,'dd' from dual;

col concatV for a20

select wmsys.wm_concat(distinct Val) as concatV from diffT;

concatV
--------
aa,bb,dd


02 文字の接続順序の指定

ListAgg関数は、文字の接続順序を指定できますが

wmsys.wm_concat関数では不可です。

select ListAgg(Val,',')

within group(order by SortKey desc) as concatV
from diffT;

concatV
------------
dd,aa,bb,aa


03 デリミタの指定

ListAgg関数は、文字の接続のデリミタを指定できますが

wmsys.wm_concat関数では不可です。

select ListAgg(Val,'***')

within group(order by SortKey desc) as concatV
from diffT;

concatV
-----------------
dd***aa***bb***aa


04 分析関数として使用した時のOrderBy指定

wmsys.wm_concat関数は、分析関数として使用した時にOrderBy指定できますが、

ListAgg関数では不可です。

select SortKey,wmsys.wm_concat(Val)

over(order by SortKey) as concatV
from diffT;

SortKey concatV
------- -----------
1 aa
2 aa,bb
3 aa,bb,aa
4 aa,bb,aa,dd


05 Keep指定

wmsys.wm_concat関数は、Keep指定が使えますが

ListAgg関数では不可です。

select wmsys.wm_concat(Val) 

Keep(Dense_Rank First order by Val) as concatV
from diffT;

concatV
-------
aa,aa