CODEWITHSUNDEEP
oracle-databaselistagg
user3138025
user3138025

Reputation: 825

Oracle LISTAGG with multiple table joins

I'm using Oracle 18.c I have a query where I join three tables. It works fine.

select ae."PRIM_KEY", 
ae."EVENT_DATE",
ae."EVENT_NAME",
ac."FIRST_NAME" || ' ' || ac."LAST_NAME" "PRESENTER"
from    "AFF_EVENT_V" ae
      , "AFF_CONTACT" ac
      , "AFF_EVENT_PRESENTER" aep
where   aep."EVENT_FKEY" = ae."PRIM_KEY"
and     aep."CONTACT_FKEY" = ac."PRIM_KEY"
Order by event_date desc
;

The query produces results like this:

PRIM_KEY EVENT_DATE EVENT_NAME PRESENTER
641 26-APR-21 Event 99 George Washington
622 19-APR-21 Event 98 John Adams
541 25-JAN-21 Event 97 Thomas Jefferson
541 25-JAN-21 Event 97 James Madison
521 18-JAN-21 Event 96 James Monroe

In many cases there are multiple Presenters for a given event. Like the Prim_Key of 541. I'd like to group the Presenters together for a given event so that the results would look like this:

PRIM_KEY EVENT_DATE EVENT_NAME PRESENTER
641 26-APR-21 Event 99 George Washington
622 19-APR-21 Event 98 John Adams
541 25-JAN-21 Event 97 Thomas Jefferson, James Madison
521 18-JAN-21 Event 96 James Monroe

I tried to use the LISTAGG function, but I can't seem to get it right. Here's what I've tried.

select ae."PRIM_KEY", 
ae."EVENT_DATE",
ae."EVENT_NAME",
LISTAGG(ac."FIRST_NAME" || ' ' || ac."LAST_NAME", ',') WITHIN GROUP (ORDER BY ac."LAST_NAME") as "PRESENTER"
from    "AFF_EVENT_V" ae
      , "AFF_CONTACT" ac
      , "AFF_EVENT_PRESENTER" aep
where   aep."EVENT_FKEY" = ae."PRIM_KEY"
and     aep."CONTACT_FKEY" = ac."PRIM_KEY"
Order by event_date desc
;

This produces an error: ORA-00937: not a single-group group function. How can I use LISTAGG when I have multiple tables joined?

Upvotes: 1

Views: 605

Answers (1)

Luc M
Luc M

Reputation: 17314

You have to add a GROUP BY clause

select ae."PRIM_KEY", 
ae."EVENT_DATE",
ae."EVENT_NAME",
LISTAGG(ac."FIRST_NAME" || ' ' || ac."LAST_NAME", ',') WITHIN GROUP (ORDER BY ac."LAST_NAME") as "PRESENTER"
from    "AFF_EVENT_V" ae
      , "AFF_CONTACT" ac
      , "AFF_EVENT_PRESENTER" aep
where   aep."EVENT_FKEY" = ae."PRIM_KEY"
and     aep."CONTACT_FKEY" = ac."PRIM_KEY"
Group BY 
ae."PRIM_KEY", 
ae."EVENT_DATE",
ae."EVENT_NAME"
Order by event_date desc
;

Upvotes: 1

Related Questions