Flattening columns in a one-to-many relationship in Oracle 10g

Question

I'm guessing this is a matter of figuring out what oracle command to use, but after a couple of hours of googling, I haven't found anything that can do what I need. So here's what I need to have happen in a nutshell:

  Table 1                   Table 2
|  PrjID   |       |  PrjID   |  UserID  |  
|----------|       |----------|----------|  
| Project1 |       | Project1 |  User1   |  
| Project2 |       | Project1 |  User2   |  
| Project3 |       | Project1 |  User3   |  
| Project4 |       | Project2 |  User2   |  
| Project5 |       | Project3 |  User5   |  

I need to generate an SQL query such that with the above two tables, I can generate a result that looks like this:

|  PrjID   |      UserIDs      |  
|----------|-------------------|  
| Project1 | User1,User2,User3 |  
| Project2 | User2             |  
| Project3 | User5             |  

The first thing I thought to try was a join statement, but as I'm sure many of you are aware, this will give a result that has multiple rows for each project. I also came across several tutorials on pivots, but that is really for just turning your result on its side and not about actually aggregating a row's string values. I'm still a bit new to the more advanced parts of SQL, so it's very possible I'm just googling the wrong things to figure out how to do this. I can probably hack together a solution in Java (this is part of a webapp) that would just iterate through the rows and pull out every user, but I'd really like to learn a way to make SQL do that part for me.

Any help offered would be very appreciated!

Answer 1

Read this page about String Concatenation in Oracle.

Assuming available, you can use wm_concat:

  SELECT a.prjid,
         wm_concat(b.userid) AS userids
    FROM TABLE_1 a
    JOIN TABLE_2 b ON b.prjid = a.prjid
GROUP BY a.prjid

Answer 2

Here is an explanation with sample code using the collect_func()

http://www.adp-gmbh.ch/blog/2005/march/28.html