with t  as(select ‘Charles‘ parent, ‘William‘ child unionselect ‘Charles‘, ‘Harry‘ unionselect ‘Anne‘, ‘Peter‘ unionselect ‘Anne‘, ‘Zara‘ unionselect ‘Andrew‘, ‘Beatrice‘ unionselect ‘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