Recursive query question - break rows into columns?
- by Stew
I have a table "Families", like so
FamilyID PersonID Relationship
-----------------------------------------------
F001 P001 Son
F001 P002 Daughter
F001 P003 Father
F001 P004 Mother
F002 P005 Daughter
F002 P006 Mother
F003 P007 Son
F003 P008 Mother
and I need output like
FamilyID PersonID Father Mother
-------------------------------------------------
F001 P001 P003 P004
F001 P002 P003 P004
F001 P003
F001 P004
F002 P005 P006
F002 P006
F003 P007 P008
F003 P008
In which the PersonID of the Father and Mother for a given PersonID are listed (if applicable) in separate columns. I know this must be a relatively trivial query to write (and therefore to find instructions for), but I can't seem to come up with the right search terms. Searching "SQL recursive queries" has gotten me closest, but I can't quite translate those methods to what I'm trying to do here.
I'm trying to learn, so multiple methods are welcome, as is vocabulary I should read up on. Thanks!