Flattening out a Group Membership Tree in SQL (with Cyclic references)

Question

I am working with a table of goups.

GroupMembers (GroupName, MemberName)

There is a row present for every group member and a group can contain other groups and users.

I would like to extract a list of GroupName, MemberName pairs where the MemberName is only a list of users. Essentially, flattening out the tree. I have done something similar to this before and manually wrote queries which exported each level's leafs out to a seperate table and then consolidated this once I had reached the last level.

The tree appears to be unbalanced and does not have any fixed number of levels. I have been looking at examples of recursive queries, and have not had much luck in implementing them.

Does anyone have any good pointers on where I can go to put togeather an elegant solution to this?

Many Thanks!

ps If it helps, I am working with SQL Server 2008.

UPDATE: I stumbled upon Recursive CTE. My only issue is that there are Cyclic references in the data :(.

This the code I used for the query:-

    WITH Members AS
    (
    --Init
    SELECT GroupName, MemberName
    FROM GroupMembers
    WHERE MemberName NOT IN (Select GroupName from GroupMembers)
    UNION ALL

    --Recursive Exe
    SELECT h.GroupName, h.MemberName
    FROM GroupMembers h INNER JOIN Members m
    ON h.MemberName = m.GroupName
    )
    Select * into GroupMembersFlattened from Members OPTION (MAXRECURSION 1500)

Is there a way to exclude the cyclic references/clense the data prior to execution of the above query?

Thanks!

Example Cyclic/Circular Reference An example of a Cyclic reference would be where the data contains the following:-

    GroupMember,     MemberName
    Group1,     Group2
    Group1,     User1
    Group2,     Group3
    Group2,     User2
    Group3,     Group1

Thanks for the tip Mikael!

Answer 1

This is how you exclude the cycles

WITH Members AS
(
--Anchor
SELECT 
    GroupName, 
    MemberName,
    0 As isCycle,
    '.' + CAST(MemberName As varchar(max)) + '.' As [path]
FROM GroupMembers
WHERE 
    MemberName NOT IN (Select GroupName from GroupMembers)

UNION ALL

--Recursive call
SELECT 
    h.GroupName, 
    h.MemberName,
    CASE WHEN m.[path] like  '%.' + CAST(h.MemberName as varchar(max)) + '.%' THEN 1 ELSE 0 END As isCycle,
    m.[path] + CAST(h.MemberName as varchar(max)) + '.' As [path]
FROM GroupMembers h 
    JOIN Members m
        ON h.MemberName = m.GroupName
WHERE
    m.isCycle = 0
)
SELECT  
    * 
FROM 
    Members
WHERE
    Members.isCycle = 0