with t  as(
select ‘Charles‘ parent, ‘William‘ child union
select ‘Charles‘, ‘Harry‘ union
select ‘Anne‘, ‘Peter‘ union
select ‘Anne‘, ‘Zara‘ union
select ‘Andrew‘, ‘Beatrice‘ union
select ‘Andrew‘, ‘Eugenie‘ 
)

SELECT parent, 
STUFF(
     ( 
      SELECT ‘,‘+ child FROM t a WHERE b.parent = a.parent FOR XML PATH(‘‘)
     ),1 ,1, ‘‘) children 
FROM t b 
GROUP BY parent