Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2036
  • Last Modified:

How can I use listagg with distinct.

Dear Experts,

My requirement is to get distinct values after I use listagg.
But distinct is not allowed inside listagg,

Please help !!
0
sakthikumar
Asked:
sakthikumar
  • 2
1 Solution
 
sdstuberCommented:
simply wrap your source data in an inline view and make that distinct, then select listagg from that

for example...

select listagg(owner,',') within group(order by owner) from
     (select distinct owner from all_objects)
0
 
Abhimanyu SuriCommented:
Can you please quote an example

If it is one row o/p combined with listagg, below example may help

SELECT LISTAGG (usr, ',') WITHIN GROUP (ORDER BY usr)
  FROM (SELECT DISTINCT username usr FROM USERS)
0
 
sakthikumarAuthor Commented:
the column already has comma separated values, so distinct is not working.

data is like below

category  email
a                email1,email2,email3
b               email2,email3,email6

when I do a group I should get only distinct email IDs
0
 
sdstuberCommented:
then you need to split your data first, then combine it afterward.


SELECT LISTAGG(email, ',') WITHIN GROUP (ORDER BY email)
  FROM (SELECT DISTINCT REGEXP_SUBSTR(
                            email,
                            '[^,]+',
                            1,
                            COLUMN_VALUE
                        )
                            email
          FROM yourdata,
               TABLE(
                       SELECT COLLECT(LEVEL)
                         FROM DUAL
                   CONNECT BY LEVEL <= REGEXP_COUNT(email, '[^,]+')
               ))

Open in new window

1

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now