Oracle string_agg(distinct columnname) does not work in pl/sql, only in sqldeveloper

Question

I am trying to get the string_agg function, detailed here to work with the distinct keyword. Sample usage:

select string_agg(distinct a.name), a.id from tbl_a a group by a.id

The problem is that this works in sqldeveloper, but when running it in application express in an anonymous pl/sql block, it refuses to accept the distinct keyword and I get an error saying that the distinct is not allowed. Why would it be doing this? Is there a simple workaround? I am using 10.2 and application express 3.2

Edit: to clarify, I am interested in solving this problem, but if there is another string_agg/group_concat function that I can use I am open to any ideas.

Edit: I eventually want to be able to do something like

select string_agg(distinct a.name),string_agg(distinct a.city), a.id from tbl_a a group by a.id

and have both string_agg columns be deduped... this is a problem when using a distinct in a subquery as it does not get the distinct values for both columns...

Answer 1

This behaviour seems to be a bug according to the last post in this AskTom thread.

Moving the distinct operation into a sub query should work:

select   string_agg(sq.name)
        ,sq.id 
from     (
         select distinct 
                a.name
               ,a.id 
         from   tbl_a a 
         ) sq
group by sq.id