Ask the Expert

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

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: