Relationship map using CTEs

For instance, if I have the following values in a table
Id ParentId
1 null
2 1
3 1
4 2
5 4
I understand how to traverse this relationship tree using CTE but how would
I build a map where I say Id 1 is related to Id 2, 3, 4, 5... Like so,
Return results
Id RelatedId
1 1
1 2
1 3
1 4
1 5
2 1
2 2
2 4
2 5
3 1
3 3
4 1
4 2
4 4
4 5
5 1
5 2
5 4
5 5
Basically, an Id is related to every id that descends from it (directly or
indirectly) and an id is related to every parent link upwards from it.
Is this possible with CTEs?"Arif" <Arif@.discussions.microsoft.com> wrote in message
news:2B66D0F9-7E43-41C1-9B93-6DF147E5EB83@.microsoft.com...
> For instance, if I have the following values in a table
> Id ParentId
> 1 null
> 2 1
> 3 1
> 4 2
> 5 4
>
> I understand how to traverse this relationship tree using CTE but how
> would
> I build a map where I say Id 1 is related to Id 2, 3, 4, 5... Like so,
>
> Return results
> Id RelatedId
> 1 1
> 1 2
> 1 3
> 1 4
> 1 5
> 2 1
> 2 2
> 2 4
> 2 5
> 3 1
> 3 3
> 4 1
> 4 2
> 4 4
> 4 5
> 5 1
> 5 2
> 5 4
> 5 5
>
> Basically, an Id is related to every id that descends from it (directly or
> indirectly) and an id is related to every parent link upwards from it.
> Is this possible with CTEs?
Yes. Here's an example of how to enumerate a transative relation using a
CTE:
Create Table T
(
ID int primary key,
ParentID int references T
)
insert into t(id,parentid)
select 1,null
union all select 2,1
union all select 3,1
union all select 4,2
union all select 5,4
go
with Related(ID, RelatedID, Distance)
as
(
--anchor with reflexive member
select ID, ID, 0 from T
--union in transitively related members
union all
select r.ID, t.ID, r.Distance + 1
from T t join Related r
on t.ParentID = r.RelatedID
)
select * from Related
order by id, RelatedID, Distance
/* results
ID RelatedID Distance
-- -- --
1 1 0
1 2 1
1 3 1
1 4 2
1 5 3
2 2 0
2 4 1
2 5 2
3 3 0
4 4 0
4 5 1
5 5 0
*/
David