patternMinor

De-Duplicate from LISTAGG

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

listaggduplicatefrom

Problem

I am using 11.2 version of oracle.
I want to de-duplicate from LISTAGG.

below is existing data in oracle db:

Speaker | Qualification | Product
P           A              P1
P           B              P2
P           C              P3
P           D              P1
P           E              P2
Q           A              P1
Q           B              P2
Q           C              P1

Want below data:

Spkeaker | Product
P           ;P1;P2;P3;
Q           ;P1;P2;

Can any one help please.

Solution

select
  speaker,
  listagg(product, ';')
    within group (order by product)
    as products 
from
  (
    select distinct speaker, product
    from existing_data
  ) t
group by speaker
order by speaker ;

Test at: dbfiddle.uk

Code Snippets

select
  speaker,
  listagg(product, ';')
    within group (order by product)
    as products 
from
  (
    select distinct speaker, product
    from existing_data
  ) t
group by speaker
order by speaker ;

Context

StackExchange Database Administrators Q#244316, answer score: 4

Revisions (0)

No revisions yet.