SQL Server 2019

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

create table data (root VARCHAR(100), parent VARCHAR(100), child VARCHAR(100));
INSERT INTO data (root, parent, child) VALUES
    ('root1', 'parent1', 'child1'),
    ('root1', 'parent1', 'child2'),
    ('root1', 'parent2', 'child1'),
    ('root1', 'parent2', 'child2'),
    ('root1', 'parent2', 'child3'),
    ('root1', 'parent3', 'child2'),
    ('root1', 'parent3', 'child3'),
    ('root1', 'parent4', 'child4'),
    ('root1', 'parent5', 'child2'),
    ('root2', 'parent6', 'child1'),
    ('root2', 'parent7', 'child5')
11 rows affected

create index ix1 on data (root, parent, child);
select
  p.root, 
  c.child,
  string_agg(d.parent, ', ') as in_parents,
  string_agg(case when d.parent is null then p.parent end, ', ') as ex_parents  
from (select distinct root, parent from data) p
join (select distinct root, child from data) c on c.root = p.root
left join data d on d.root = p.root and d.parent = p.parent 
                and d.root = c.root and d.child = c.child
group by p.root, c.child
order by p.root, c.child


root
child
in_parents
ex_parents


root1
child1
parent1, parent2
parent3, parent4, parent5

root1
child2
parent1, parent2, parent3, parent5
parent4

root1
child3
parent2, parent3
parent1, parent4, parent5

root1
child4
parent4
parent1, parent2, parent3, parent5

root2
child1
parent6
parent7

root2
child5
parent7
parent6

root	child	in_parents	ex_parents
root1	child1	parent1, parent2	parent3, parent4, parent5
root1	child2	parent1, parent2, parent3, parent5	parent4
root1	child3	parent2, parent3	parent1, parent4, parent5
root1	child4	parent4	parent1, parent2, parent3, parent5
root2	child1	parent6	parent7
root2	child5	parent7	parent6