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
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
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/
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sharath,
why did you use distinct ?
why did you use distinct ?
SQLServer, MySQL, etc.