June 24, 2014 at 3:47 pm
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 @TV
select 'John', 'Mary'union all
select 'Mary', 'Jessica' union all
select 'Peter', 'Albert' union all
select 'Peter', 'Nancy' union all
select 'Abby', null union all
select 'Jessica', 'Fred' union all
select 'Joe', 'Frank'
I would like my result to look like this:
familyID Name
1 John
1 Mary
1 Jessica
1 Fred
2 Peter
2 Albert
2 Nancy
3 Abby
4 Joe
4 Frank
Thanks a lot!!
June 25, 2014 at 7:52 am
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.
June 26, 2014 at 9:52 am
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 @TV
select 'John', 'Mary'union all
select 'Mary', 'Jessica' union all
select 'Jessica', 'Mary'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply