SQL Server : Using recursive CTE to resolve group membership

Question

I have one table (users_groups) :

+-----------+------------+---------+
| groupGUID | memberGUID | isGroup |
+-----------+------------+---------+
|  32AB160C |   5B277276 |       0 |
|  32AB160C |   0A023D1D |       0 |
|  5C952B2E |   32AB160C |       1 |
|  4444FTG5 |   5C952B2E |       1 |
+-----------+------------+---------+

isGroup column indicates whether memberGUID is a group or not.

I want to obtain a new table (new_users_groups) with all group memberships resolved :

+-----------+------------+
| groupGUID | memberGUID |
+-----------+------------+
|  32AB160C |   5B277276 |
|  32AB160C |   0A023D1D |
|  5C952B2E |   5B277276 |
|  5C952B2E |   0A023D1D |
|  4444FTG5 |   5B277276 |
|  4444FTG5 |   0A023D1D |
+-----------+------------+

For now, I'm doing everything manually :

Looking for all group's memberGUID

SELECT * FROM users_groups WHERE isGroup = 1;
For all groups returned by previous step, find its members

SELECT * FROM users_groups WHERE groupGUID = '5C952B2E'
If members are not groups, insert them into a new table

INSERT INTO new_users_groups (groupGUID, memberGUID) VALUES ('5C952B2E', '5B277276'); INSERT INTO new_users_groups (groupGUID, memberGUID) VALUES ('5C952B2E', '0A023D1D');
If members are groups, go to step 2.

How can I automate this? Maybe with a Recursive CTE ?

It might help if you explain what the logic is for resolving memberships. As of now, we would have to dig into your insert logic and try to tease that out. — Tim Biegeleisen, Aug 08 '18 at 11:36
Ok, I tried to be more explicit by adding an explanation for each step. It should be easier to understand for readers. — Trevor65, Aug 08 '18 at 11:46

Gordon Linoff · Accepted Answer · 2018-08-08T11:42:51.550

6

You can do this with a recursive CTE:

with cte as (
  select ug.groupGUID, ug.groupGUID as grp, ug.memberGUID
  from user_groups ug
  where isGroup = 0
  union all
  select ug.groupGUID, ug.groupGUID as grp, cte.memberGUID
  from user_groups ug join
       cte
       on cte.grp = ug.memberGUID
 )
select groupGUID, memberGUID
from cte;

Here is a Rextester.

edited Aug 08 '18 at 11:42

answered Aug 08 '18 at 11:36

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Aug 08 '18 at 11:45

0

Try CROSS JOIN

SELECT t1.groupGUID, t2.memberGUID 
FROM temp t1, temp t2 WHERE t2.isGroup = 0
GROUP BY t1.groupGUID, t2.memberGUID

answered Aug 08 '18 at 11:45

akshay

777
4
15

SQL Server : Using recursive CTE to resolve group membership

2 Answers2