Problem solve Get help with specific problems with your technologies, process and projects.

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
--  -----  -----
 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.

Dig Deeper on Oracle and SQL