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:
SELECT Country, FirstName FROM Employees ORDER BY Country, FirstName
I get following result:
Country FirstName UK Anne UK Michael UK Robert UK Steven USA Andrew USA Janet USA Laura USA Margaret USA Nancy
Now I want to combine first names into comma separated strings grouped by country.Take a look at following SQL code:
SELECT DISTINCT Country, (STUFF((SELECT ',' + FirstName FROM Employees E2 WHERE E1.Country = E2.Country ORDER BY FirstName FOR XML PATH(''), TYPE, ROOT).value('root','nvarchar(max)'),1,1,'')) as FirstNames 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:
Country FirstNames UK Anne,Michael,Robert,Steven USA Andrew,Janet,Laura,Margaret,Nancy
UPDATE 4/25/2012: If your task is opposite – you need to ungroup “deaggregate” string values into separate rows, there is
an app for that a solution that does just that.