Ed Klein-385786 (11/12/2008)
I'm just saying it would be nice. Do you have to go to that much effort to sum a numeric field? Why shouldn't accumulating text be as easy.
It's far from impossible, but the main issue is that you can't quite predict results as you're used from working with numbers.
Assume you'd be aggregating 3 records. The SUM would do 1 + 2 + 3 and the result would be 6. It doesn't really matter if internally it does 1 + 2 + 3 or 1 + 3 + 2 or 3 + 2 + 1 or etc...
However, if you're accumulating text, then it does become relevant in what order the records get processed. A + B + C = ABC is a different result from A + C + B = ACB, or C + B + A = CBA etc...
Given the way SQLCLR aggregations are coded it gets difficult quickly on how you want to implement this in order to get a consistent result; for things like COUNT(), SUM(), AVG(), MIN(), MAX() order has no influence and you can simply 'stream' through the input updating some working variables that then get returned in the end (after some minor processing). The amount of resources needed for this is rather low. But, if you want to have consistent results for the ACCTEXT() by e.g. sorting the values first, this means you have to bring them ALL in memory first, sort them along the way, then copy everything into one giant string and then return that again. And as if that's not bad enough already, if the Query Optimizer would decide to split the work over multiple threads this also includes merging the (presorted) data in the final step, sort it again and then do the conversion to one big string.
It's not un-doable, in fact I bet that if you google for it you're bound to find some implementations that are likely more worked out than what I just typed out of my head above. But from a "what's going on behind the scenes" point of view things could get ugly really fast I think.