dynamic sub query with stuff

1.  Here MDTM_Site  and MDTM_Hierarchy  is a table
2. create #temphierachy   temporary table
3. stuff use for comma seprated value in one row sitename wise

select ms.SiteID ,ms.sitename, mh.LinkedToSite as linksite,
(select SiteName from MDTM_Site where siteid=mh.linkedtosite) as LinkedSiteName
into #temphierachy
from MDTM_Hierarchy mh join MDTM_Site ms on mh.SiteID=ms.SiteID
where mh.NetworkID=@NetworkId and mh.IsDeleted=0

      select distinct sitename, siteid ,
        STUFF((SELECT distinct ', ' + LinkedSiteName
         FROM #temphierachy where sitename = t.sitename
       
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(255)')
        ,1,1,'')  AS LinkSiteName
       
      from #temphierachy t order by t.sitename DESC