T-SQL String Aggregate in SQL Server

T-SQL dialect of SQL doesn't have aggregate functions for strings, but there is an easy workaround using magic of XML.

Consider Employees table of the Northwind database. When I run following query:

1	SELECT Country, FirstName FROM Employees ORDER BY Country, FirstName

I get following result:

01	Country FirstName

02	UK      Anne

03	UK      Michael

04	UK      Robert

05	UK      Steven

06	USA     Andrew

07	USA     Janet

08	USA     Laura

09	USA     Margaret

10	USA     Nancy

Now I want to combine first names into comma separated strings grouped by country.Take a look at following SQL code:

1	SELECT DISTINCT Country,

2

3	(STUFF((SELECT ',' + FirstName FROM Employees E2 WHERE  E1.Country = E2.Country ORDER BY FirstName

4	FOR XML PATH(''), TYPE, ROOT).value('root[1]','nvarchar(max)'),1,1,'')) as FirstNames

5

6	FROM Employees E1 ORDER BY Country

The internal subquery is the important part. The FOR XML PATH clause combines data into a single output, the ,TYPE, ROOT statements make sure that the output is of XML type with a root node. And the .value method converts output into varchar to display characters like "<" or ">" correctly. The result:

1	Country  FirstNames

2	UK       Anne,Michael,Robert,Steven

3	USA      Andrew,Janet,Laura,Margaret,Nancy