Highlighted

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

LEGEND ,
Aug 23, 2016

Copy link to clipboard

Copied

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,

^_^

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,

^_^

TOPICS
Database access

Views

352

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

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

LEGEND ,
Aug 23, 2016

Copy link to clipboard

Copied

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,

^_^

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,

^_^

TOPICS
Database access

Views

353

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Aug 23, 2016 0
LEGEND ,
Aug 23, 2016

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 23, 2016 1