Smoothing a tree of group membership in SQL (with circular references)

Question

I work with a goups table.

GroupMembers (GroupName, MemberName)

There is a line for each member of the group, and the group may contain other groups and users.

I would like to extract a list of pairs of GroupName, MemberName, where MemberName is just a list of users. Essentially tree alignment. I did something similar to this and manually wrote queries that exported each level into a separate table and then consolidated it as soon as I reached the last level.

The tree seems unbalanced and does not have a fixed number of levels. I looked at examples of recursive queries and did not have much luck in their implementation.

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

Many thanks!

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

UPDATE: I came across a recursive CTE. My only problem is that the data has circular references: (.

This code that I used for the request:

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 circular references / clense data before executing the above query?

Thanks!

Example Circular / Circular Reference An example of a circular reference is where the data contains the following: -

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

Thanks for the tip of Mikael!

Answer 1

This is how you exclude loops

 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 

Answer 2

I would like to extract a list of GroupName, MemberName, where MemberName is just a list of users.

I probably don't understand your structure here, but for me it seems like you only need to do this.

 SELECT GroupName, MemberName FROM GroupMembers WHERE MemberName NOT IN (Select GroupName from GroupMembers) 

This will give you all the leaf nodes. If a group can be a worksheet, or if a user can have members, you need something else to distinguish between users and groups.

Result with your data:

 GroupName MemberName --------- ---------- Group1 User1 Group2 User2