How to convert STRING_AGG to stuff function to work query on SQL Server 2016 below?

Question

I have below SQL query which is not working on SQL Server 2016 and below version

SELECT
    (
        SELECT STRING_AGG(d.Name, ',') AS divnames 
        FROM (
            SELECT div.name, MHLId 
            FROM MsrtProfile mp 
            INNER JOIN Division div ON mp.DivisionId = div.Id 
            WHERE mp.mhlid = ph.potentialHospitalNo  
            GROUP BY div.Name, MHLId
        ) d
    ) AS divisionnames
FROM xyz ph

Example my output will come like below

div1,div2,div3

I need with comma values of single column rows.

I have two tables are following below - first table name is Hospital:

hospitalId   name
-----------------
1            a1
2            a2

Second table name is division

id  DivisionName    hospitalId
------------------------------
1     d1             1
2     d2             1
3     d3             2

I need an output like below by join first and second table by hospitalid

DivisionName
-------------
d1,d2
d3

Answer 1

Seems like a pretty straight-forward query with STRING_AGG and GROUP BY:

SELECT 
    h.HospitalId, h.Name,
    STRING_AGG(d.DivisionName, ',') AS DivisonName
FROM 
    dbo.Division d
INNER JOIN
    dbo.Hospital h ON h.HospitalId = d.HospitalId
GROUP BY
    h.HospitalId, h.Name

UPDATE

For versions before SQL Server 2017, you need to use the FOR XML PATH approach - something like this:

SELECT 
    h.HospitalId, h.Name,
    STUFF ((SELECT ',' + d.DivisionName
            FROM @Division d
            WHERE d.HospitalId = h.HospitalId
            FOR XML PATH('')), 1, 1, '') 
FROM 
    @Hospital h 
GROUP BY
    h.HospitalId, h.Name