Hi Nick,
You can do it this way: first, create a field in the Polygon table that will contain the point IDs (e.g. point_list). Then, after you carry out your join (to create a temp table called joinedTable), issue the following update statement:
Update joinedTable Set point_list = IIf(point_list="", point, point_list + ";" + point)
This assumes your polygon ids (1 to 4) are in a field called "polygon" and your point ids (a,b,c etc) are in a field called "point", as in your example above. The update statement will update the point_list field within the Polygon table. Where there is a one to many link, point ids will be concatenated. To avoid inserting a ";" at the beginning, the IIf statement is used so that concatenation of the ";" separator only occurs if the field has already been populated with at least one point id. I hope this explanation makes sense!
------------------------------
James Nolet
Dooley Mitchell & Morrison Pty Ltd
Mentone, VIC, Australia
------------------------------
Original Message:
Sent: 11-17-2020 06:54
From: Nick Hall
Subject: SQL - loops?
Hi,
not sure if there's been a discussion about this before but I was trying to find a Pro solution for this issue.
I want to turn the results of a spatial join like this:
------------------------------
Nick Hall
Mapchester LTD
nick.hall@mapchester.co.uk
------------------------------