Q

A one-to-many relationship as a comma-delimited list

In a one-to-many relationship, what is the best way to write a query to return the many table as a comma separated...

list along with the primary table. Currently I cursor through the many tables and update the parent. Is there a better method?

Person Table
ID  FNAME  LNAME
--  -----  -----
 1  John   Doe
 2  Mary   Jane

Sports table ID PersonID Sport -- -------- ----- 1 1 Soccer 2 1 Football 3 2 Baseball
Returns: FNAME LNAME SPORTS ----- ----- ------ John Doe Soccer,Football Mary Jane BaseBall

Looping through the result set, for example by using a cursor, is indeed the best way to produce this type of output. (I'm not sure what you mean by "update the parent" because that would not be advisable in a normalized relational database.)

When you process each row, you have to do "current/previous" logic, to detect a control break -- in your example, to detect a new person. On a control break, you output the previous control, and initialize the current one. There are two "gotchas" in the logic, because you don't have a previous control to compare with the very first row, and also after the last row, you don't have a current control. But this kind of logic is bread-and-butter stuff for competent programmers.

For an easier way to do it, see Denormalizing a result set (11 June 2001) for a similar example using ColdFusion, which does the looping and control break checking for you automatically.


This was last published in September 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close