I often need to do string concatenation or geometry unions over a column in SQL Server 2008 and I'm aware that you can write custom aggregate functions in .NET and register them with SQL Server to do these things.
However, you can take a very simple approach to the problem using a local variable and a select e.g.
CREATE TABLE Test( ID int IDENTITY(1,1) NOT NULL PRIMARY KEY, geom geometry NOT NULL, attribute nvarchar(max) NOT NULL, ) GO INSERT INTO Test(geom, attribute) VALUES ('POLYGON ((0 0, 1 0, 2 2, 0 0))', 'shape1'), ('POLYGON ((0 0, 0 1, 2 2, 0 0))', 'shape2'), ('POLYGON ((2 2, 3 2, 3 3, 2 3, 2 2))', 'shape3') GO -- string concatenation DECLARE @mytext nvarchar(MAX) = ''; SELECT @mytext = @mytext + ' ' + attribute FROM Test; SELECT @mytext; -- geometry union DECLARE @mygeom geometry = 'POLYGON EMPTY'; SELECT @mygeom = @mygeom.STUnion(geom) FROM Test; SELECT @mygeom;
Nulls etc. aside, these seem to work fine. So I don't understand why these methods aren't suggested at all in most articles.
What is wrong with calculating aggregates values like this?
Thanks very much.