Concatenating Results in SQL Query

Below is an example to concatenate results in an SQL query:

SELECT S.FirstName, S.LastName,
  STUFF( 
    ( SELECT ',' + S1.FirstName + ' ' + S1.LastName FROM Student S1 WHERE S1.FirstName = S.FirstName FOR XML PATH('') ), 
    1, 
    1, 
    ''
  ) AS AnyStudentsWithSimilarNames
FROM Student S

In the example, we use FOR XML PATH('') to concatenate the result from multiple rows into a single value.

We also use STUFF() function to simply remove the first occurrence of ',' character. The syntax is STUFF( expression, starting_character_position, length, replace_with_expression ).

If we have '<', '>' or '&' characters in our projection and want to avoid those getting encoded, we can replace the codes inside STUFF() function to:

(SELECT ... FOR XML PATH(''), TYPE).VALUE('.','VARCHAR(MAX)')

Here we add TYPE to have the query with FOR XML PATH() returns XML data type then we use VALUE() function to get the value. Both are used as a work around to avoid the characters getting encoded.