UnionAggregate on geometry datatype to fuse geometry

Question

I have a list of geometry fields in a sql table with some other information. I want to fuse the geometry of many rows into one when my query matches a certain id.

I try the UnionAggregate method, but sql server doesn't seems to know what this function is. Also, I don't know if I can fuse geometry that are not connected directly together.

Could you tell me what is wrong with my query and tell me if I can fuse my geometry fields?

My query:

SELECT a.fid, b.name, a.geometry::UnionAggregate(a.geom) 
FROM tableA a inner join tableB b on a.fid=b.fid 

Answer 1

Try using it like this: geography::UnionAggregate()

Answer 2

The following works for me :

SELECT
  a.fid, 
  b.name, 
  GEOMETRY::UnionAggregate(a.geom) as 'Ageo'
FROM a 
INNERJOIN b on (a.fid=b.fid) 
GROUP BY
  a.fid, 
  b.name