How to concatenate text from multiple rows into a single text string in SQL server

In the following we have two tables with one-to-many relationships.
As a result we concatenate the data from the subtable, to reseive a single row.

select x.IdContatto, x.CognomeContatto,   
LEFT(x.S,Len(x.S)-1) As RagSoc1
From (
Select distinct Contatti.IdContatto, Contatti.CognomeContatto,    
       rel.CodAnag as rel_CodAnag,
       (
            SELECT ST1.RagSoc1 + ',' AS [text()]
            FROM ANAnDatiGen ST1
            WHERE ST1.CodAnag = rel.CodAnag
            ORDER BY ST1.CodAnag
            FOR XML PATH ('')
        ) S
from ANAnContatti Contatti
inner join ANAnContattiRel rel on Contatti.IdContatto = rel.IdContatto
inner join ANAnDatiGen gen on rel.CodAnag = gen.CodAnag
) x

Table 1

1 | Mutschlechner

Table 2

1 | GMBH

1 | AG

Result

1 | Mutschlechner | GMBH,AG

With SQL Server 2017+ and SQL Azure: STRING_AGG

Small example, not testet!

Without grouping

SELECT STRING_AGG(Name, ', ') AS myNames
FROM HumanResources.Name;

With grouping

SELECT GroupName, STRING_AGG(Name, ', ') AS myNames
FROM HumanResources.Name
GROUP BY myGroupName;

With grouping and sub-sorting

SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS myNames
FROM HumanResources.Name
GROUP BY myGroupName;