Friday, 7 November 2014

Concatenating Results in SQL Query

Below is an example to concatenate results in an SQL query:
SELECT S.FirstName, S.LastName,
    ( SELECT ',' + S1.FirstName + ' ' + S1.LastName FROM Student S1 WHERE S1.FirstName = S.FirstName FOR XML PATH('') ), 
  ) 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:
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.

No comments: