This thread is to discussion what is difference between
wmsys.wm_concat and
ListAgg. B-)
*************************************************************************
difference1 :-)
wmsys.wm_concat allows distinct option.
ListAgg does not allows it.
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
*************************************************************************
difference2 :-)
ListAgg allows to decide string concat order.
wmsys.wm_concat does not allows it.
select ListAgg(Val,',')
within group(order by sortKey desc) as concatV
from diffT;
CONCATV
------------
dd,aa,bb,aa
*************************************************************************
difference3 :-)
ListAgg allows to decide delimiter.
wmsys.wm_concat does not allows it.
select ListAgg(Val,'***')
within group(order by sortKey desc) as concatV
from diffT;
CONCATV
-----------------
dd***aa***bb***aa
*************************************************************************
difference4 :-)
wmsys.wm_concat allows to be used OLAP function with order by
ListAgg does not allows it.
ListAgg allows only OLAP function without order by.
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
*************************************************************************
difference5 :-)
wmsys.wm_concat allows to be used KEEP
ListAgg does not allows it.
select wmsys.wm_concat(Val)
Keep(Dense_Rank First order by Val) as concatV
from diffT;
CONCATV
-------
aa,aa