The trick here is where to start so I used the logic where members are not related to someone (relatedTo is null) or they are related to someone but the member is not on the relatedTo side (John, Peter, Abby, Joe) and consider them head of the family. The rest is to traverse the hierarchy and rank them based on the hof.
with Tree as (
select distinct
A.member as hof, A.member, A.member as relatedTo
from
@TV as A
where
(
A.relatedTo is null
or exists (
select *
from @TV as B
where B.member = A.member and B.relatedTo is not null
)
)
and not exists (
select *
from @TV as B
where B.relatedTo = A.member
)
union all
select
P.hof,
C.member,
C.relatedTo
from
Tree as P
inner join
@TV as C
on P.relatedTo = C.member
and C.relatedTo is not null
)
select
DENSE_RANK() over (order by hof) as rnk,
hof,
relatedTo
from
Tree
order by
rnk,
case when relatedTo = hof then 1 else 2 end,
relatedTo
GO
That is the closer I got.