Is a SQL String Aggregate possible in arcgis?

Question

I have created a feature class from the results of a 1:many join between a table and a polygon layer in ArcGIS 10.3. The polygon layer contains land parcels and the table contains names. Where a 1:many join was present, ArcGIS created another row so for example if "John" and "Robert" both were owners, two rows would be created- one for John one for Robert.

I would now like to aggregate the results into a single field, so that I again only have 1 geometry containing a field with comma separated values for names e.g.

Parcelid: "10"
names: "John,Robert"

• Vince: One-to-many is fraught with danger if you go against the grain of ArcGIS's unique registered rowid constraint. It's important to note that ArcGIS didn't "duplicate the geometry" without being told to do so (and it isn't duplicated as much as just being drawn twice). Please edit the question to contain your SQL and your first draft at your attempted SQL to implement the desired functionality. It will help if you think in terms of rows, with columns (one of which is geometry), instead of geometries that contain fields (which is never the case).

Answer 1

There is only one tool that produces a concatenated list from a 1:M relationship, and that is Spatial Join. You can use that tool if you do the following:

Assuming you have ArcGIS version 10.1 or above and that the polygons do not overlap, join the table to the polygons and export them into a geodatabase to convert the 1:M relationship into a 1:1 set of features for all of the related records.

Use the Buffer tool to apply a small negative buffer to the exported output.

Use the Spatial Join tool by making the original polygons the target and the buffer output the Join features. Use the One-To-One option. In the Field map, right click the names field and open its properties. Select the Join option for the Merge Rule, make the field 255 characters long, and set the delimiter character to a comma. Run the tool and you will have your field.

• Robert Buckley: It was in fact much easier than I anticipated. I used the Spatial Join Tool as you recommended and set the parameter to JOIN_ONE_TO_ONE and added a new Field, set the merge field to join and the delimiter to comma.

Answer 2

I think you should be able to use SQL similar to the code posted in this thread and use it to create an Arcmap Query Layer.

Answer 3

You could write a python/arcpy script to perform this one-to-many value write on the parcel layer. The script flow would go something like this:

1. Use update cursor on parcel layer
2. Next, nest search cursor to search against name table within update cursor
3. Use "if" conditional logic from parcel id row value to evaluate against row value from parcel id on name table
4. If condition from step 3 is true, concatenate row value of parcel owner name column with owner name from name table
5. Continue looping...

with arcpy.da.UpdateCursor('Parcels', ['parcelID', 'owner']) as cursorParcel: with arcpy.da.SearchCursor('Owner', ['parcelID', 'owner']) as cursorOwn: for rowParcel in cursorParcel: for rowOwn in cursorOwn: if rowParcel[0] == rowOwn[0]: rowParcel[1] = '{}, {}'.format(rowParcel[1], rowOwn[1]) cursorParcel.updateRow(rowParcel)