Perl JOIN-like behavior in Oracle?

Question

I have two tables, let's call them PERSON and NAME.

 PERSON
  person_id
  dob

 NAME
  name_id
  person_id
  name

And let's say that the NAME table has data like:

name_id  person_id  name
1        1          Joe
2        1          Fred
3        1          Sam
4        2          Jane
5        2          Kim

I need a query (Oracle 10g) that will return

name_id   names
1         Joe, Fred, Sam
2         Jane, Kim

Is there a simple way to do this?

Update:

According to the article that figs was kind enough to provide, starting in 9i you can do:

SELECT wmsys.wm_concat(dname) departments FROM dept;

For this example, the answer becomes:

SELECT name_id,  wmsys.wm_concat(name) from names group by name_id

Beware. The output type changed on wm_concat() in recent patches from varchar to lob. Casting it back to char works though. — Daren Schwenke, Oct 15 '11 at 06:12
Take a look at http://www.williamrobertson.net/documents/one-row.html — cs95, Jul 19 '19 at 05:18
You might find [this article](http://www.williamrobertson.net/documents/one-row.html) to be helpful. — user10340, Sep 19 '08 at 21:40

score 0 · Answer 1 · edited May 23 '17 at 10:27

0

The short answer is to use a PL/SQL function. For more details, have a look in this post.

edited May 23 '17 at 10:27

Community

answered Sep 21 '08 at 01:53

Mike McAllister

1 Answers1