SQL - Multiple Rows as Single Column, Comma-Separated List

Let's say I have the following rows pulled from the database - based on multiple tables being joined:

select
   p.firstname, p.lastname, t.type
from person p
inner join person_type_xref ptx on ptx.person_id = p.person_id
inner join type t on t.type_id = ptx.type_id;

Tom     Smith     Lion
Tom     Smith     Tiger
Tom     Smith     Bear

How can I pull this same list to look like this:

Tom     Smith     Lion, Tiger, Bear

I'm using Oracle 10g Express Edition - I have seen examples of putting together a comma-separated list like this pulling data from a column in a table, but couldn't see how you did this when joining in multiple tables.
rooeydanielAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please see this article on how to do in oracle:
http://halisway.blogspot.com/2006/08/oracle-groupconcat-updated-again.html
0
 
schwertnerCommented:
see here:   http://forums.oracle.com/forums/thread.jspa?threadID=2205545&tstart=15

Possibly the solution  03/27/06  can help you.
0
 
rooeydanielAuthor Commented:
Took a few minutes to re-engineer the example here, but in the end it works great and does exactly what I needed it to.
0
 
slightwv (䄆 Netminder) Commented:
Until you end up with a string over 4000 characters...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.