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

Posted on 2011-04-27
Last Modified: 2012-05-11
Let's say I have the following rows pulled from the database - based on multiple tables being joined:

   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.
Question by:rooeydaniel
    LVL 142

    Accepted Solution

    please see this article on how to do in oracle:
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    LVL 47

    Expert Comment

    see here:

    Possibly the solution  03/27/06  can help you.

    Author Closing Comment

    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.
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    Until you end up with a string over 4000 characters...

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    Via a live example, show how to take different types of Oracle backups using RMAN.
    This video shows how to recover a database from a user managed backup

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now