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

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

Query:

DECLARE @StudentNames VARCHAR(MAX) = '';
SELECT 	@StudentNames = IIF(S.StudentName IS NULL, @StudentNames, IIF(@StudentNames = '', S.StudentName, @StudentNames + ', ' + S.StudentName))
FROM 	dbo.Students S

Output:

Peter,Paul,Mary

Note: This query handles null values also.

Full Example using table variable:

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

DECLARE @StudentNames VARCHAR(MAX) = '';
SELECT 	@StudentNames = IIF(StudentName IS NULL, @StudentNames, IIF(@StudentNames = '', StudentName, @StudentNames + ', ' + StudentName))
FROM 	@Students

SELECT @StudentNames AS [Student Names]

Output:

Peter,Paul,Mary

SQL Server 2017+ and Azure SQL: STRING_AGG

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

Output:

Peter,Paul,Mary

With Sorting:

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

Output:

Mary,Paul,Peter