Devart Forums

Code: Select all

SELECT
	p.Id AS [datasetItemId],
	p.HashKeyId AS [hashKeyId],
	(
		SELECT
			geography::UnionAggregate(l.geography)
		FROM Location pod
		WHERE l.PlaceId = p.Id
	) AS geography,
	(
		SELECT
			p.Id,
			p.FileNumber,
			p.Owner,
			p.Type,
			p.Status,
			p.SurfaceSubBasin,
			p.GroundWaterBasin,
			p.LinkToSummary,
			p.HashKeyId,
			ISNULL(JSON_QUERY(
					(
						SELECT
							l.Id,
							l.PlaceId,
							l.NameOrNumber,
							l.Status,
							l.GroundWaterSource,
							l.DepthOfWellFeet,
							l.DepthToWaterFeet,
							l.UTMSource,
							l.UTMAccuracy,
							l.OtherLocation,
							l.LocationError,
							l.LinkToSummary,
							l.Geography.STAsText() AS Geography
						FROM dbo.Location l 
						WHERE l.PlaceId = p.Id
						FOR JSON PATH
					)
				), '[]') AS Locations
		FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES
	) AS datasetItem
FROM dbo.Place p