SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable(
   grp    VARCHAR(40) NOT NULL
  ,member VARCHAR(40) NOT NULL
);
INSERT INTO mytable(grp,member) VALUES ('Group-A','User-1');
INSERT INTO mytable(grp,member) VALUES ('Group-A','User-2');
INSERT INTO mytable(grp,member) VALUES ('Group-B','User-3');
INSERT INTO mytable(grp,member) VALUES ('Group-B','User-4');
INSERT INTO mytable(grp,member) VALUES ('Group-C','Group-A');
INSERT INTO mytable(grp,member) VALUES ('Group-C','Group-B');
INSERT INTO mytable(grp,member) VALUES ('Group-D','Group-C');
INSERT INTO mytable(grp,member) VALUES ('Group-D','User-5');
INSERT INTO mytable(grp,member) VALUES ('Group-E','User-6');
9 rows affected

declare @value as varchar(40)
set @value = 'Group-D'

;with CTE as (
        select 1 as lvl, @value as member, cast(null as varchar(40)) as parent
        union all                  
        select CTE.lvl + 1, t.member, t.grp
        from CTE
        inner join mytable as t on CTE.member = t.grp
        where lvl < 10 
        )
select
    lvl, member, parent
from CTE
order by lvl, member
;


lvl
member
parent


1
Group-D
null

2
Group-C
Group-D

2
User-5
Group-D

3
Group-A
Group-C

3
Group-B
Group-C

4
User-1
Group-A

4
User-2
Group-A

4
User-3
Group-B

4
User-4
Group-B


with CTE as (
        select distinct 1 as lvl,  grp as member, cast(null as varchar(40)) as parent
        from mytable as t
        where not exists (select null
                          from mytable as c
                          where t.grp = c.member)
        union all                  
        select CTE.lvl + 1, t.member, t.grp
        from CTE
        inner join mytable as t on CTE.member = t.grp
        where lvl < 10 
        )
select
    lvl, member, parent
from CTE
order by lvl, member
;


lvl
member
parent


1
Group-D
null

1
Group-E
null

2
Group-C
Group-D

2
User-5
Group-D

2
User-6
Group-E

3
Group-A
Group-C

3
Group-B
Group-C

4
User-1
Group-A

4
User-2
Group-A

4
User-3
Group-B

4
User-4
Group-B

lvl	member	parent
1	Group-D	null
2	Group-C	Group-D
2	User-5	Group-D
3	Group-A	Group-C
3	Group-B	Group-C
4	User-1	Group-A
4	User-2	Group-A
4	User-3	Group-B
4	User-4	Group-B

lvl	member	parent
1	Group-D	null
1	Group-E	null
2	Group-C	Group-D
2	User-5	Group-D
2	User-6	Group-E
3	Group-A	Group-C
3	Group-B	Group-C
4	User-1	Group-A
4	User-2	Group-A
4	User-3	Group-B
4	User-4	Group-B