I’ve occasionally needed to use SQL to create a comma-delimited list of values from a query. There were a few hacks that could be used, but luckily SQL Server 2017 added the STRING_AGG function that will concatenate all of the values from a designated column, using a specified delimiter:

SELECT type_desc, STRING_AGG(name, ', ') as names
FROM sys.objects
GROUP BY type_desc
ORDER BY type_desc;

I ran across a situation where a needed to use this functionality, but I needed the items to be in alphabetical order. I had to check the documentation, but it turned out there is a WITHIN GROUP option that allowed me to set the order:

SELECT type_desc, STRING_AGG(name, ', ') 
    WITHIN GROUP(ORDER BY name) as names
FROM sys.objects
GROUP BY type_desc
ORDER BY type_desc;

Reference:
Microsoft – STRING_AGG