Oracle: Use LEFT OUTER JOIN, but convert outer data to a list
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,
^_^
