Search code examples
sqloraclelistagg

Can you filter a listagg to only return multiple results?

I have an SQL query I'm running on SQL Developer that uses a listagg to return one or more items for a single field.

As it is, the query returns records in which the listagg has one or more items.

What I would like to do is filter out the records in which the listagg has only one item in favor of only returning records in which the listagg has MORE THAN one item.

This is the part of the query that uses the listagg - along with some psuedo-code of what I want to do:

Select "SingleItem", listagg(distinct "MultipleItems", ', ') as "MultipleItems"
FROM my_results mr
WHERE EXISTS
(SELECT "SingleItem", "MultipleItems"
   FROM my_results
)
AND <"MultipleItems" contains more than 1 item>

Any suggestions?

Thanks much.

Solution

  • I think you could just use HAVING COUNT(DISTINCT

    SELECT
  "SingleItem",
  listagg(distinct "MultipleItems", ', ') as "MultipleItems"
FROM my_results mr
GROUP BY
  "SingleItem"
HAVING COUNT(DISTINCT "MultipleItems") > 1;