Oracle: Use LEFT OUTER JOIN, but convert outer data to a list

[WolfShade]

Hello, all,

I know this can be done; I just can't remember how I did it, oh so many years ago. 

Presume tables exist for groups and people.  People can belong to more than one group.

SELECT g.groupName, p.lastName || ', ' || p.firstName as fullName
FROM groups g LEFT OUTER JOIN groupPersonAssociation gpa ON gpa.groupID = g.groupID
              LEFT OUTER JOIN person p ON p.personID = gpa.personID
ORDER BY g.groupName, fullName

This gives us:

Group One          Alpha, Daniel
Group One          Bravo, Charles
Group One          Charlie, Chuck
Group Two          Beta, Alpha
Group Two          Delta, Bonnie
Group Three        Echo, Bunny
Group Three        Golf, Samuel
Group Three        November, Stan

How to word the SQL in such a way as to get the data like:

Group One          Alpha, Daniel | Bravo, Charles | Charlie, Chuck
Group Two          Beta, Alpha | Delta, Bonnie
Group Three        Echo, Bunny | Golf, Samuel | November, Stan

V/r,

^_^

[WolfShade · Accepted solution]

I finally figured it out.  I was using incorrect keywords on Google.

SELECT g.groupName, LISTAGG(p.lastName || ', ' || p.firstName,' | ') WITHIN GROUP (ORDER BY g.groupName) "fullName"  
FROM groups g LEFT OUTER JOIN groupPersonAssociation gpa ON ggpa.groupID = g.groupID  
              LEFT OUTER JOIN person p ON p.personID = gpa.personID  
GROUP BY g.groupName
ORDER BY g.groupName, fullName  

Just in case anyone else runs across this same desire.

HTH,

^_^