Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 799
  • Last Modified:

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.
0
rooeydaniel
Asked:
rooeydaniel
1 Solution
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now