StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POFlattening columns in a one-to-many relationship in Oracle 10g
text
Body
copied!<p>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: </p> <pre> Table 1 Table 2 | PrjID | | PrjID | UserID | |----------| |----------|----------| | Project1 | | Project1 | User1 | | Project2 | | Project1 | User2 | | Project3 | | Project1 | User3 | | Project4 | | Project2 | User2 | | Project5 | | Project3 | User5 | </pre> <p>I need to generate an SQL query such that with the above two tables, I can generate a result that looks like this: </p> <pre> | PrjID | UserIDs | |----------|-------------------| | Project1 | User1,User2,User3 | | Project2 | User2 | | Project3 | User5 | </pre> <p>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. </p> <p>Any help offered would be very appreciated!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload