STRING_AGG alternative

Question

Our SQL Server version does not support STRING_AGG because of the version

Our data is returned like this

enter image description here

But our requirement is to display it like this:

enter image description here

This is the code I believe would work should we have the functionality:

SELECT
        studentid,
        STRING_AGG(code, ',') AS Codes
    FROM
        testtable
    GROUP BY
        studentid;

Any help appreciated.

I tried this using the XML answers:

SELECT
      studentid,code
     = STUFF((
          SELECT ',' + dbo.testtable .code 
          FROM dbo.testtable 
                    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM dbo.testtable  
GROUP BY StudentID
ORDER BY StudentID

This returns this which is sort of right but I need to have it only attaching the Students Codes not all the codes in the database:

updated results

Answer 1

You are missing the correlation in your subquery. e.g.

SELECT
      t1.studentid,
      code = STUFF((
                SELECT ',' + t2.code 
                FROM dbo.testtable  AS t2
                WHERE t2.studentid = t1.studentid -- Need this part
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM dbo.testtable AS t1   
GROUP BY t1.StudentID
ORDER BY StudentID;

Example on db<>fiddle