How to recursively expand rows in sql server?

Question

In MS SQL Server, if I have 2 tables like so

Group
------------
ID GroupName
1  A
2  B
3  C

GroupMembership
---------------
Value GroupID  Type
1     2        Node
3     2        Node
2     1        Group
4     1        Node
5     3        Node

In the membership table, if the type is Node, then it is just a normal value. But if its type is Group, then it is referring to the group defined in the Group table where the Value is the GroupID.

So the above querying for GroupID=1, would result in this kind of tree

4, (1, 3)

But in theory I could make any kind of tree with any amount of levels of nested brackets, by correctly setting up the group references between both tables.

The question is, how can I do a select statement for GroupID=1, such that it would recursively expand all the related records (even deeply nested ones) and return the membership records?

So ideally it would return

1     2        Node
3     2        Node
2     1        Group
4     1        Node

My current query only returns this as it doesn't know how to expand the rows recursively.

2     1        Group
4     1        Node

What is a simple sql query to do this?

Does this answer your question? [How to create a MySQL hierarchical recursive query?](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — philipxy, May 28 '22 at 11:07

score 1 · Answer 1 · answered May 28 '22 at 10:03

You should use Common table expression with recursion:

DECLARE @GroupMembership TABLE
(
    Value   INT,
    GroupID INT,
    Type    VARCHAR(5)
);

INSERT INTO @GroupMembership (Value, GroupID, Type   )
                      VALUES (1    , 2      , 'Node' ),
                             (3    , 2      , 'Node' ),
                             (2    , 1      , 'Group'),
                             (4    , 1      , 'Node' ),
                             (5    , 3      , 'Node' );


DECLARE @requestingGroupID INT = 1;

WITH Recursion AS (
    SELECT Value, GroupID, Type
    FROM @GroupMembership
    WHERE GroupID = @requestingGroupID
    UNION ALL
    SELECT M.Value, M.GroupID, M.Type
    FROM Recursion AS R
    INNER JOIN @GroupMembership AS M ON R.Value = M.GroupID AND R.Type = 'Group'
)
SELECT *
FROM Recursion

Result:

Value	GroupID	Type
2	1	Group
4	1	Node
1	2	Node
3	2	Node

How to recursively expand rows in sql server?

1 Answers1