Link to home
Start Free TrialLog in
Avatar of Fin Vangelis
Fin Vangelis

asked on

sql, best method get child nodes in same record as parent

I have a parent table and child table. it is a one to many relationship.

child IDs are in one field. I want to convert each child ID into it's own field by concatenating child and position (child + #, i.e. child1) and join all the new child fields to the parent field.
I would then get a row of parent and child fields. there can be any number of children.

i.e.
parent ID, child1ID, child2ID, etc. for each unique child of a parent.

parent| child1| child2| child3|
John, Jimmy, Jan,Jerry
Don, Danny
Nancy, Mary, Mike
Avatar of Dustin Saunders
Dustin Saunders
Flag of United States of America image

What database platform are you using?

SQLServer, MySQL, etc.
Avatar of Fin Vangelis
Fin Vangelis

ASKER

sql server
There are several examples out there creating a CSV using the FOR XML trick.

Change the comma with the pipe:
https://blog.sqlauthority.com/2009/11/25/sql-server-comma-separated-values-csv-from-table-column/
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
sharath,
why did you use distinct ?