Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Recursive family, but how? Expand / Collapse
Author
Message
Posted Tuesday, June 24, 2014 3:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 5:24 PM
Points: 7, Visits: 21
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!!
Post #1585707
Posted Wednesday, June 25, 2014 7:52 AM This worked for the OP Answer marked as solution
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:10 PM
Points: 115, Visits: 628
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.



Post #1585900
Posted Thursday, June 26, 2014 9:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 5:24 PM
Points: 7, Visits: 21
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
)
)

Post #1586519
Posted Thursday, June 26, 2014 9:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 5:24 PM
Points: 7, Visits: 21
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'
Post #1586540
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse