Skip to main content
WolfShade
Legend
August 23, 2016
Answered

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

  • August 23, 2016
  • 1 reply
  • 579 views

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,

^_^

This topic has been closed for replies.
Correct answer WolfShade

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

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

Just in case anyone else runs across this same desire.

HTH,

^_^

1 reply

WolfShade
WolfShadeAuthorCorrect answer
Legend
August 23, 2016

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

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

Just in case anyone else runs across this same desire.

HTH,

^_^