Recursive query question - break rows into columns?

Question

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!

Answer 1

In SQL Server, the feature you use to solve recursive problems like these are Common Table Expressions.

Answer 2

You can self join the table twice with outer joins to get what you want:

SELECT
    T1.FamilyID AS FamilyID,
    T1.PersonID AS PersonID,
    T2.PersonID AS Father,
    T3.PersonID AS Mother
FROM Families T1
LEFT JOIN Families T2
    ON T1.FamilyID = T2.FamilyID
   AND T1.Relationship IN ('Son', 'Daughter')
   AND T2.Relationship = 'Father'
LEFT JOIN Families T3
    ON T1.FamilyID = T3.FamilyID
   AND T1.Relationship IN ('Son', 'Daughter')
   AND T3.Relationship = 'Mother'

Result:

FamilyID  PersonID  Father  Mother  
F001      P001      P003    P004    
F001      P002      P003    P004    
F001      P003      NULL    NULL    
F001      P004      NULL    NULL    
F002      P005      NULL    P006    
F002      P006      NULL    NULL    
F003      P007      NULL    P008    
F003      P008      NULL    NULL