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) 

Answer 2

This isn't a cross product; it is a recursive/hierarchical query. Assuming that you have no cycles in the relationships, you can do:

with cte as (
      select id, id as groupmember
      from table t
      where parent is not null
      union all
      select cte.id, t.id as groupmember
      from table t join
           cte
           on cte.parentid = t.id
    )
select *
from cte;