Ability to concatenate strings when aggregating data

[rory]

I haven't seen this in any product, but would love to have the ability to to specify concatenate as an aggregation method for strings.

Suppose you have 1000 clients, and a hundred employees have relationships with them, typically two or three per client (but maybe 20).  You want to get a list of clients, revenue generated from them and employees who know them, one line per client.  If you try and pivot this you get an unmanageable large number of columns, and you may not be interested in this, since the first thing you do is concatenate them back to a single field.  However, you're then tied to the data when doing the concatenate so when the new sales person comes on board you're modifying code.

I figure you would need to have two additional parameters for this function - max string length, and separator character.

Incidentally, does anyone have a generic solution to this?  I find myself looking at stored procedures and cursors to get the required results.