1 person found this helpful
Not sure whether it works with listagg, but it does with wm_concat :
SQL> with t as (
2 select 'A' f1, 'x' f2 from dual union all
3 select 'A', 'x' from dual union all
4 select 'A', 'y' from dual union all
5 select 'A', 'z' from dual union all
6 select 'B', 'x' from dual union all
7 select 'B', 'y' from dual union all
8 select 'B', 'y' from dual union all
9 select 'C', 'x' from dual union all
10 select 'C', 'y' from dual union all
11 select 'C', 'y' from dual union all
12 select 'C', 'z' from dual)
13 select wm_concat(distinct f2)
14 from t
15 group by f1
http://psoug.org/definition/WM_CONCAT.htm says "Note that WM_CONCAT is undocumented and unsupported by Oracle, meaning it should not be used in production systems."
But even so it is at least very useful in ad-hoc throw away queries.
Actually i don't have any 11gR2 to test listagg, so i just meant that you should try adding the distinct in the listagg parameter (because it worked on wm_concat, which looks similar )
Maybe in this example. But if f1 and f2 was large expressions instead of short column names you would want to avoid repeating them.
...Although making a sub-query to give f1 and f2 short aliases would work in that case, that would mean complicating stuff too. So a short keyword like distinct in wm_concat would be very welcome in listagg too, I'm almost sure it doesn't exist.
Sorry, LISTAGG doesn't do DISTINCT. If you want to use LISTAGG, then you have to do a sub-query first, to get distinct values.
If you're tempted to use WM_CONCAT, then copy the STRAGG function from
STRAGG seems to do exactly what WM_CONCAT does. (WM_CONCAT is undocumented, so we don't actually know what it does.) STRAGG is very handy, but, unfortunately, it doesn't put its results in order.
SYS_CONNECT_BY_PATH (shown in the same AskTom page mentioned above) can be used to get distinct, ordered results. You would probably need a sub-query to get the results you want, so, if you have Oracle 11.2 or higher, you might as well use LISTAGG.