CODEWITHSUNDEEP
sqloracle-databaselistagg
Maya Saias
Maya Saias

Reputation: 37

listagg in oracle sql

I have this sql query in Oracle:

SELECT listagg(A.name, ",") within group (ORDER BY A.id) as A,
listagg(B.name, ",") within group (ORDER BY B.id) as B
FROM some_table
join A on A.id = some_table.id
join B.name = some_table.name
where some_table.status in (1,2,3)
group by some_table.id

My problem is I get duplicated results from the listagg which I do not want. How can I fix that? Thank you!

Upvotes: 0

Views: 2127

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Oracle does not support distinct for listagg() prior to Oracle 19.

In your case, though, the problem is presumably caused by a Cartesian product as a result of the joins. That suggests that correlated subqueries are a better approach:

select t.*,
       (select listagg(a.name, ',') within group (order by a.id)
        from A a
        where a.id = t.id 
       ) as a,
       (select listagg(b.name, ',') within group (order by b.id)
        from B b
        where b.id = t.id 
       ) as b
from some_table t
where t.status in (1, 2, 3)
group by t.id;

Upvotes: 2

Alex R.
Alex R.

Reputation: 276

Listagg function doesn't support distinct option. You should use subquery for select unique values.

Upvotes: 0

Related Questions