This is yet another handy function, which can make one's programming easier.

Concatenate Many Rows Into A Single String

Let us compare two simple queries:

As you can see, the second query aggregates values by a GROUP BY clause.

What do we get from that? Let's see:

Neat.

Even more: if you want to assemble a dynamic query for copying data from one table to another? Simply enumerate to common columns and wrap it into INSERT and SELECT.

Where It Came From

The original source was taken from here. The post pretty much explains the mechanism of an MS SQL aggregate for CLR:

• the init of a new group;
• the accumulate of new values;
• the merge with the group;
• the terminate of a group;
• the read and write to serialize the struct.

What Is The Difference?

The mechanism didn't work sometimes. You would expect it to return a,b,c,d,e,f, but it gave you a,b,cd,e,f — for some strange reasons. It occured on bulky sets: there's a suggestion it involves multithreading. The solution was to copy the delimiter from other groups.

Aggregate properties:

• IsInvariantToNulls = true: nulls don't change the result
• IsInvariantToDuplicates = false: duplicates change the result
• IsInvariantToOrder = false: order changes the result

If you find it useful, feel free to use.