Relationships are complicated.

PE has a relationships feature, which can link clients to each other using relationship types (and reciprocal types...) - Mostly I use these at my place of work to track seat-mates and spouses. If you have a large group of people that like to attend together, but pay separately, this is one way to handle the data.  The problem is that it is difficult to report on this data, since you can end up with any number of structures depending on how the links are entered. Ideally you want something that looks like an asterisk, with one group leader as the center that links to everyone else.  Usually this is not the case - you end up with circles, trees, and other shapes that look like tangles of string. Here is a script I wrote that will take this and put into groups of patrons, using the person with the most connections as the leader (or if it is a circular link, the lowest account number is chosen.)  If one were to run this on facebook, it would put 99% of people in Tom's group (or whoever that default friend dude is) - but these are closed groups of seat-mates, so not everyone is linked. (seven seatmates to Kevin Bacon anyone?)

Anyway- here are the goods - this takes the relationship type as input.  This script assumes the same reciprocal and main relationship type for each link.
declare @rtype int

set @rtype = 5

create table #l (cltcode int, link int, lnkgrp int, lnkgrpleader int)

insert into #l (cltcode,link)
select relcltparentid, relcltrelationid from relationships where relfromtype = @rtype or reltotype = @rtype
union select relcltrelationid , relcltparentid from relationships where relfromtype = @rtype or reltotype = @rtype

update #l set lnkgrp = case when cltcode < link then cltcode else link end
declare @i int
set @i = 0
while (
exists (select 1 from #l l1 inner join (select cltcode, lnkgrp from #l) as l2 on l2.cltcode = l1.link and l2.lnkgrp < l1.lnkgrp)
or exists (select 1 from #l l1 inner join (select link, lnkgrp from #l) as l2 on l2.link = l1.cltcode and l2.lnkgrp < l1.lnkgrp)
) and @i < 10 --limit loop to 10 iterations (degrees of separation).
begin
update l1 set lnkgrp = l2.lnkgrp
from #l l1 inner join (select cltcode, lnkgrp from #l) as l2 on l2.cltcode = l1.link and l2.lnkgrp < l1.lnkgrp

update l1 set lnkgrp = l2.lnkgrp
from #l l1 inner join (select link, lnkgrp from #l) as l2 on l2.link = l1.cltcode and l2.lnkgrp < l1.lnkgrp
set @i = @i + 1
end

update #l set lnkgrpleader = lm.gpwinner
from #l as l1 inner join
(select l1.cltcode,
(select top 1 cltcode from #l as l2 where l2.lnkgrp = l1.lnkgrp group by cltcode order by count(*) desc, cltcode ) as gpwinner
from #l as l1)
as lm on lm.cltcode = l1.cltcode

create table #gl (acct int , lnkgrp int, isgroupleader bit, lname varchar(999), fname varchar(999))
insert #gl (acct, lnkgrp, isgroupleader)
select cltcode, lnkgrp,
case when cltcode = lnkgrpleader then 1 else 0 end as isgroupleader
from #l group by cltcode, lnkgrp, case when cltcode = lnkgrpleader then 1 else 0 end
order by lnkgrp, case when cltcode = lnkgrpleader then 1 else 0 end desc, cltcode

update #gl set lname = cltsurname, fname = cltfirstname
from #gl inner join clients on cltcode = acct

--select acct from #gl group by acct having count(*) > 1

select * from #gl

drop table #gl, #l