## Recursive family, but how?

 Author Message SeeknFind Grasshopper Group: General Forum Members Points: 13 Visits: 41 Hi,I have a family table and would like to group all related members under the same familyID. This is a replication of existing business data, 14,000 rows. The familyID can be randomly assigned to any group, its sole purpose is to group the names:`declare @tv table (member varchar(255), relatedTo varchar(255))insert into @tvselect 'John', 'Mary'union allselect 'Mary', 'Jessica' union allselect 'Peter', 'Albert' union allselect 'Peter', 'Nancy' union allselect 'Abby', null union allselect 'Jessica', 'Fred' union allselect 'Joe', 'Frank' `I would like my result to look like this:`familyID Name1 John1 Mary1 Jessica1 Fred2 Peter2 Albert2 Nancy3 Abby4 Joe4 Frank`Thanks a lot!! hunchback SSC-Enthusiastic Group: General Forum Members Points: 117 Visits: 639 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 relatedTofrom @tv as Awhere ( 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 allselect P.hof, C.member, C.relatedTofrom 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, relatedTofrom Treeorder by rnk, case when relatedTo = hof then 1 else 2 end, relatedToGO`That is the closer I got. SeeknFind Grasshopper Group: General Forum Members Points: 13 Visits: 41 This is great!! Thanks.Just one note, this part of the code below I think always resolves as true and I am not sure what it was supposed to do:` ( A.relatedTo is null or exists ( select * from @tv as B where B.member = A.member and B.relatedTo is not null ) )` SeeknFind Grasshopper Group: General Forum Members Points: 13 Visits: 41 One more note, my data is not normalized. I discovered the scenario below which causes and endless recursion. I can clean it up but just thought I would mention it.`declare @tv table (member varchar(255), relatedTo varchar(255))insert into @tvselect 'John', 'Mary'union allselect 'Mary', 'Jessica' union allselect 'Jessica', 'Mary'`