SUM() Strings in SQL server

It's really easy to sum numeric values. You write this:

SELECT SUM(revenue) FROM orders

Every so often I wish I could write a similar query for strings. For example, wouldn't it be neat if we could get a list of employees by department like this?

SELECT TO_LIST(name), department FROM employees GROUP BY department

As it turns out, there's a way to do this, but it's a little bit tricky. You have to use an XML feature to do something it wasn't really designed to do. By converting results into XML and back, you can produce lists exactly the way you might want. Here's what the query looks like:

SELECT department
, SUBSTRING(
(SELECT ', ' + name
FROM employees e2
  WHERE e2.department = e1.department
  FOR XML PATH(''))
, 2, 999) AS list_of_names
FROM employees e1
GROUP BY department

Why does this work? First, you're doing a main query that just lists all the departments. So you get a list like this:

department
-----------
Sales
Accounting
Development

Then, for each record in this list, we do a subquery listing "', ' + name" for each record. That produces these results internally:

field1
-----------
, Bob
, Chris
, Alex

The XML path with a blank parameter turns that into a gigantic text string, like so:

field1
------------------
, Bob, Chris, Alex

And of course the Substring removes the first two characters. The end result is that you get exactly the list you expect!

(http://www.tedspence.com/index.php?entry=entry090724-095142)