Chris Miller Blog

RocketScientist's Miscellaneous Ramblings

Aggregate Functions

I've run into this before, I ran into it today, and I'll probably run into it again.

Why isn't there an aggregate function that concatenates strings?

Besides the obvious "string don't get that big" (which I'd manage by manipulating my group by to fit) why not?

Something like:

CONCAT_AGG(column, separator)

Where separator is a varchar that determines what goes between each string (space, comma-space, whatever).

This would be pretty dang handy.

Legacy Comments

Alex K
re: Aggregate Functions

re: Aggregate Functions
I wondered that too, and just created a SQLCLR custom aggregation function to do it.. works pretty slick..

re: Aggregate Functions
Or, if you are on SQL Server 2005 or later, use the FOR XML clause?

Rob Farley
re: Aggregate Functions
...because there's no support for ORDER BY in aggregate functions yet.

If you're going to concatenate strings, you need to tell it what order to aggregate them, right?