Home » SQL & PL/SQL » SQL & PL/SQL » Join one-to-many fields in a single row (Oracle 10g)
Join one-to-many fields in a single row [message #355801] Mon, 27 October 2008 11:17 Go to next message
plsqlll
Messages: 6
Registered: October 2008
Junior Member
Hi,

I'm new to PL/SQL and i'm not sure how I can resolve the following problem.

I have two tables:
Table: Users
Table: User Sites

When I join the tables, I get the following result:

User | Site
----------
User A | Site 1
User A | Site 2
User B | Site 1

I want the result to be as follows:

User | Sites
------------
User A | Site 1, Site2
User B | Site 1

I think I need to loop through the result and check if the user is the same as the previous row and concatenate the value and then insert the row in a temp table and return the result? How can I easily resolve this?

I need to do this in Oracle PL/SQL, because I can't do it in the source code of my program. A standard component that we use requires just a result set.

Thanks much!!

[Updated on: Mon, 27 October 2008 11:18]

Report message to a moderator

Re: Join one-to-many fields in a single row [message #355803 is a reply to message #355801] Mon, 27 October 2008 11:22 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
There are multiple ways of doing it something like that, it has been answered quite often, here for example.

Or search for "stragg"
Re: Join one-to-many fields in a single row [message #355804 is a reply to message #355803] Mon, 27 October 2008 11:33 Go to previous messageGo to next message
plsqlll
Messages: 6
Registered: October 2008
Junior Member
Thanks! It works great...
I'm definitely going to check out your other links. Interesting information.

Learned something today that's been bothering me for a long time...I used to solve this in my app code Smile

[Updated on: Mon, 27 October 2008 11:34]

Report message to a moderator

Re: Join one-to-many fields in a single row [message #355838 is a reply to message #355804] Mon, 27 October 2008 22:28 Go to previous message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,

even stragg concate_all does not work in your system as there are many solutions offered in orafaq since i have raised the same querry and got the results. search it will be interesting
yours
dr.s.raghunathan
Previous Topic: Need help with a query
Next Topic: select from a table minus other table?
Goto Forum:
  


Current Time: Mon Dec 05 02:41:32 CST 2016

Total time taken to generate the page: 0.08199 seconds