How do I intentionally get the equivalent of a 'cross product' (in SQL Server)?

Question

I have a parent child relationship managed in my database like this:

ID   |   ParentID    
A    |    <NULL>    
B    |    A    
C    |    A    
E    |    <NULL>    
F    |    E    

I want to build a table valued function that will return a table that essentially allows you to easily determine every other item that is part of your 'family' (it isn't terribly important to keep track of the parent, but it would be nice if that could be part of the result) looks like this:

ID   | Group Member    
A    |    A    
A    |    B    
A    |    C    
B    |    A    
B    |    B    
B    |    C  
C    |    A    
C    |    B    
C    |    C  
E    |    E    
E    |    F    
F    |    E    
F    |    F

You can see, I essentially want to match every record that is part of a 'family' with every other member that is part of a family. This just screams to me that there is a reasonably simple way to do this, but I have just been beating my head against a wall with this

Answer 1

A Join On ISNULL(Parent,ID) for the table with itself should return the desired result.

declare @a Table (ID varchar(10),Parent Varchar(10))
Insert into @a Values ('A',NULL) ,('B' , 'A'),('C','A'),('E',NULL) ,('F','E')
Select a.ID,b.ID  as GroupMember
from @a a
JOIN @a b ON ISNULL(a.Parent,a.ID)=ISNULL(b.Parent,b.ID)