In a one-to-many relationship, what is the best way to write a query to return the many table as a comma separated...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.