How to concatenate text from multiple rows into a single text string in the SQL Server 2017 and later?

By using STRING_AGG function, we can concatenate text from multiple rows into a single text string without writing any custom SQL queries. The separator will not add at the end of the string. 

This function introduced in SQL Server 2017.

Syntax:

STRING_AGG(expression, separator) Returns nvarchar(max);

Consider a database table dbo.Students holding student names, with four rows:

Example 1:

SELECT 	STRING_AGG(S.StudentName, ',') AS [Student Names]
FROM 	@Students S

Output:

Peter,Paul,Mary

Example 2: With Sorted list

SELECT 	STRING_AGG(S.StudentName, ',') WITHIN GROUP (ORDER BY S.StudentName) AS [Student Names]
FROM 	@Students S

Output:

Mary,Paul,Peter

Full Query:

DECLARE @Students TABLE(StudentName VARCHAR(100))
INSERT INTO @Students(StudentName)
VALUES ('Peter'),
(NULL),
('Paul'),
('Mary')

SELECT 	STRING_AGG(S.StudentName, ',') AS [Student Names]
FROM 	@Students S

-- With Sorted list
SELECT 	STRING_AGG(S.StudentName, ',') WITHIN GROUP (ORDER BY S.StudentName) AS [Student Names]
FROM 	@Students S

Note: The GROUP BY clause is required if the STRING_AGG function isn't the only item in the SELECT list.

Generate list of emails per towns

The following query finds the email addresses of employees and groups them by towns:

SELECT 		town, STRING_AGG (email, ';') AS emails  
FROM 		dbo.Employee  
GROUP BY 	town;

Output: