Microsoft SQL Server The STUFF Function Using FOR XML to Concatenate Values from Multiple Rows
Example
One common use for the FOR XML function is to concatenate the values of multiple rows.
Here's an example using the Customers table:
SELECT
STUFF( (SELECT ';' + Email
FROM Customers
where (Email is not null and Email <> '')
ORDER BY Email ASC
FOR XML PATH('')),
1, 1, '')
In the example above, FOR XML PATH('')) is being used to concatenate email addresses, using ; as the delimiter character. Also, the purpose of STUFF is to remove the leading ; from the concatenated string. STUFF is also implicitly casting the concatenated string from XML to varchar.
Note: the result from the above example will be XML-encoded, meaning it will replace < characters with < etc. If you don't want this, change FOR XML PATH('')) to FOR XML PATH, TYPE).value('.[1]','varchar(MAX)'), e.g.:
SELECT
STUFF( (SELECT ';' + Email
FROM Customers
where (Email is not null and Email <> '')
ORDER BY Email ASC
FOR XML PATH, TYPE).value('.[1]','varchar(900)'),
1, 1, '')
This can be used to achieve a result similar to GROUP_CONCAT in MySQL or string_agg in PostgreSQL 9.0+, although we use subqueries instead of GROUP BY aggregates. (As an alternative, you can install a user-defined aggregate such as this one if you're looking for functionality closer to that of GROUP_CONCAT).
