gerg-520419 (1/31/2011)

---

WITH GroupMembers (Bottom, Child, ParentGroup, Level, hierarchypath)

AS

(

-- Anchor member definition

SELECT g.child as Bottom, g.child, g.parent, 0 AS Level, convert(varchar(max), g.child + '->' + g.parent) AS hierarchypath

FROM childrenandparents AS g

WHERE child not in (select parent from childrenandparents)

UNION ALL

-- Recursive member definition

SELECT gm.bottom, g.child, g.parent, Level + 1, hierarchypath + '->' + g.parent

FROM childrenandparents as g

INNER JOIN GroupMembers AS gm

ON gm.parentgroup = g.child

where hierarchypath not like '%'+g.child +'->' + g.parent + '%'

)

select bottom, parentgroup, level, hierarchypath

from groupmembers

where hierarchypath like '%'+parentgroup + '->' + '%'

order by level desc

option(maxrecursion 100);

will select just cycles. Needn't to calculate 'correct' max level beforehand.

eb3e->b->c->d->b

ab3a->b->c->d->b