Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

LISTAGG with DISTINCT option

ApexBineApr 4 2016 — edited Jul 31 2019

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

MKJ10930279

NOT WORKING IN 11.2.0.3.

even the columns mentioned in WHERE clause is not present.

William Robertson

Perhaps that's because Oracle hasn't taken up AB's suggestion and backported it to 11.2.0.3 yet.

ApexBine

NOT WORKING IN 11.2.0.3.
even the columns mentioned in WHERE clause is not present.

MKJ, are we talking about the first statement? Actually, I would expect that to work in any 11.2 version.

Is the view sys.all_identifiers there at all?

Anyway, this example should work (and show some duplicates) if you have the HR schema:

select d.department_name,

listagg(e.salary ,', ') within group (order by e.salary) salaries

from employees e

join departments d

on d.department_id = e.department_id

group by d.department_name

MKJ10930279

MKJ, are we talking about the first statement? Actually, I would expect that to work in any 11.2 version.
Is the view sys.all_identifiers there at all?
Anyway, this example should work (and show some duplicates) if you have the HR schema:
select d.department_name,
listagg(e.salary ,', ') within group (order by e.salary) salaries
from employees e
join departments d
on d.department_id = e.department_id
group by d.department_name

Hi,

The second statement is throwing error, on DISTINCT, ORA-00907; missing right parenthesis.

Structure is fine.

ApexBine

Hi,
The second statement is throwing error, on DISTINCT, ORA-00907; missing right parenthesis.
Structure is fine.

Well, the second example is just a possble syntax for my suggestion. It won't work in a current database.

1 person found this helpful

MKJ10930279

Well, the second example is just a possble syntax for my suggestion. It won't work in a current database.

If that is the case, then its a good idea.

1 person found this helpful

Rafiq D

Somehow, I think you can use an outer select using the WITH clause to group the result before using the listagg. This will do the trick

Sven W.

Somehow, I think you can use an outer select using the WITH clause to group the result before using the listagg. This will do the trick

Yes in general it is possible to do such a distinct by stacking up two listagg functions. But it is clumsy to develop and not so easy to implement when more then 1 column needs to be aggregated.

Chris Hunt

Yes in general it is possible to do such a distinct by stacking up two listagg functions. But it is clumsy to develop and not so easy to implement when more then 1 column needs to be aggregated.

You don't need two listagg functions, you just need to do a distinct on the underlying table(s). So instead of this:

select deptno,listagg (sal,', ') within group (order by sal)
from scott.emp
group by deptno;

do this

select deptno,listagg (sal,', ') within group (order by sal)
from (select distinct deptno,sal from scott.emp)
group by deptno;

ApexBine

You don't need two listagg functions, you just need to do a distinct on the underlying table(s). So instead of this:
select deptno,listagg (sal,', ') within group (order by sal)
from scott.emp
group by deptno;
do this
select deptno,listagg (sal,', ') within group (order by sal)
from (select distinct deptno,sal from scott.emp)
group by deptno;

Hi @"Chris Hunt",

this is fine as long as I want to concatenate only one column.

I'm still looking for a solution for concatenating two columns (separately)

or for using listagg with "over (partition by ...)"

Sven W.

You don't need two listagg functions, you just need to do a distinct on the underlying table(s). So instead of this:
select deptno,listagg (sal,', ') within group (order by sal)
from scott.emp
group by deptno;
do this
select deptno,listagg (sal,', ') within group (order by sal)
from (select distinct deptno,sal from scott.emp)
group by deptno;

An example:

Here I have some test data for projects and teams.

I want to have a list of all team leaders and a second list of all team members. "Paul" is a team leader in several teams, but I want him only to be listed once. This can not be easily solved useing the current logic. (it is possible). Bines suggestion would be simliar to the COUNT(distinct column) logic and would solve such scenarios very easily.

with projects as (select 1 project_id, 'IoT4.0' project_name from dual union all

select 2 project_id, 'VeryBigData' project_name from dual union all

select 3 project_id, 'Cloudify' project_name from dual )

,teams as (select 1 team_id, 1 project_id, 'John' membername, 'Y' is_leader from dual union all

select 2 team_id, 1 project_id, 'Paul' membername, 'Y' is_leader from dual union all

select 3 team_id, 1 project_id, 'George' membername, 'N' is_leader from dual union all

select 4 team_id, 1 project_id, 'Ringo' membername, 'N' is_leader from dual union all

select 5 team_id, 2 project_id, 'John' membername, 'N' is_leader from dual union all

select 6 team_id, 2 project_id, 'Wayne' membername, 'N' is_leader from dual union all

select 7 team_id, 2 project_id, 'Paul' membername, 'Y' is_leader from dual union all

select 8 team_id, 3 project_id, 'Paul' membername, 'Y' is_leader from dual union all

select 9 team_id, 3 project_id, 'Simon' membername, 'N' is_leader from dual union all

select 10 team_id, 3 project_id, 'Art' membername, 'N' is_leader from dual union all

select 11 team_id, 3 project_id, 'Garfunkel' membername, 'N' is_leader from dual

)

select count(distinct p.project_id) project_count

,listagg(case when is_leader = 'Y' then membername end,',') within group (order by membername) leaders

,listagg(case when is_leader = 'N' then membername end,',') within group (order by membername) members

from projects p

left join teams t on t.project_id = p.project_id

group by ();

Result:

PROJECT_COUNT	LEADERS	MEMBERS
3	John,Paul,Paul,Paul	Art,Garfunkel,George,John,Ringo,Simon,Wayne