I love the LISTAGG function which makes my life a lot easier.
And I would love it even more if it was able to omit duplicates!
So instead of writing
select name, signature, type, listagg(usage,', ') within group (order by usage) usages
from sys.all_identifiers
where owner = 'ME'
and object_name = 'PKG_EXAMPLE'
and object_type = 'PACKAGE BODY'
group by name, signature, type
which would return something like
'DECLARATION, REFERENCE, REFERENCE, REFERENCE, REFERENCE, REFERENCE'
I would write
select name, signature, type, listagg(usage,', ' DISTINCT) within group (order by usage) usages
from sys.all_identifiers
where owner = 'ME'
and object_name = 'PKG_EXAMPLE'
and object_type = 'PACKAGE BODY'
group by name, signature, type
and expect
'DECLARATION, REFERENCE'.
What do you think of it? Would it be a useful amendment?
This feature is coming with 19c:
SQL> select deptno, listagg (sal,', ') within group (order by sal)
2 from scott.emp
3 group by deptno;
DEPTNO LISTAGG(SAL,',')WITHINGROUP(ORDERBYSAL)
---------- ---------------------------------------
10 1300, 2450, 5000
20 800, 1100, 2975, 3000, 3000
30 950, 1250, 1250, 1500, 1600, 2850
SQL> select deptno, listagg (distinct sal,', ') within group (order by sal)
2 from scott.emp
3 group by deptno;
DEPTNO LISTAGG(DISTINCTSAL,',')WITHINGROUP(ORDERBYSAL)
---------- -----------------------------------------------
10 1300, 2450, 5000
20 800, 1100, 2975, 3000
30 950, 1250, 1500, 1600, 2850