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 first published in September 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close