Combine multiple results into a single string in SQL Server

Question

Is it possible to combine multiple results into a single string in SQL Server?

I have the following table:

NameID Name
------ -----
1      Alvin
2      Alan
3      Ben
4      Tom

How can I select the table and return the select result as below:

Group
-----
Alvin, Alan, Ben and Tom

Answer 1

SQL Server 2017 introduced the STRING_AGG to simplify this process:

SELECT STRING_AGG(Name,', ') FROM Table

This will return:

Alvin, Alan, Ben, Tom

There is a good summary on these concepts here as well: SQL Server: Concatenate Multiple Rows Into Single String

If it is absolutely necessary to inject the and in the end, then we could use a replace last instance of routine:
NOTE: this level of formatting is much better managed in the rendering logic, not the SQL data logic.

SELECT STUFF(names,LastIndex,2,' and ') 
FROM (
    SELECT STRING_AGG(Name,', ') names  
    FROM Table
) as raw
CROSS APPLY (SELECT LEN(names)-CHARINDEX(' ,', REVERSE(names)) as LastIndex) as Helper

Returns:

Alvin, Alan, Ben and Tom