How to dissolve rows based on a common ID, and retain all attribute fields ?

[NikeshPatel]

Hoping to find some help.

I have a table of records that assigns a record of work to each individual Shape in a street centerline table where there is a common ID match (Made using SQL, below). There are multiple shapes for each record of work which means the SQL pulls in over 1000 records for only a 100 different record of work performed. 

I want to condense that table so there is only one multipart feature for each record, but also maintain all the information from the record. If I use tools such as Dissolve, join features etc, all attribute information is lost. 

SELECT 
Table A. WorkID
Table B. EID
Table C. Shape
   
FROM 
       Table A

	inner join 
	
	Table B
	
 on  Table A.[WORKID] = Table B.[WORKID]  and TABLE A.[DESCRIPTION] like 'Quote'

 inner JOIN 
    Table C
ON 
    Table C.[ID] = Table B.[EID]
	
	 order by WE.[WORKORDERID] DESC

Is there a way in SQL to join all the shape fields together, or what geoprocessing steps can I take to get to my end result?

[RichardHowe]

Nikesh. I would suggest a dissolve is the right way to go. Presumably you are dissolving on the Work_ID field so you end up with one polygon per Work_ID. You then have a couple of options in order to relink your tabular data, depending on how you would like it presented. You need to make a decision about how you would like to see this one to many relationship:

1. You can create a relate between the dissolved feature class (right click layer, Joins and Relates, Add Relate) and the table records using Work_ID (present in both datasets) as the Relate field. This will allow you to click any individual polygon and see each of the tabular records associated with it in isolation.

2. Perform a "Summary Statistcs" operation on the records table with Work_ID as your Case Field and set a statstictics rule to merge text fields with a delimiter or output averages, min, max etc, for numeric fields - to leave you one row per WorkID which you could then use the "Join FIelds" tool to permanently add back to your feature class (again using Work_ID" as the join field.

[NikeshPatel]

Hi Richard,
Thanks for your response.

I had been over thinking and getting lost in the weeds. The dissolve pairwise tool has a merge option for First, last etc on the summery fields. Outside of some field name cleanups, the data comes over pretty smoothly. Wanted the data together as the ideal scenario is to have interactive dashboards for both internal and public.