I am trying to re-arrange my standalone table so I can calculate a new field. I want to calculate the difference between the 2020 count and the 2007 for each state. To do this, I would like to create a 'State' field, a '2007 count' field, and a '2020 count' field. I am not sure what the best way to do this is. I don't want the states to repeat like they do in my current table. I would like each state to have a 2007 count and a 2020 count, instead of having two of the same states (one with a 2007 count and one with a 2020 count).
I created a Feature Class in a SQL Server enterprise GDB that has this table:
The SQL Select statement below will creates a table in my database called BogusStatesFormat that looks like this:
Select State, , ,(-) as YearDiff Into BogusStateFormat from ( Select State, MyCount, Year FROM [TESTSTATES] ) src pivot ( Sum(MyCount) for Year in (,) ) piv;
If you don't need the final data to be a feature class your done. If you want to create a new feature class containing this data, you could use the Delete Identical tool on A COPY OF YOUR STATES FEATURE CLASS (Delete Identical modifies the input data you enter into the tool) to get just one state polygon for each state. Then you could use the state name to join the resulting new state feature class to the new table in SQL server containing your year counts and difference. Then you could export that joined feature class to a new feature class.
Hope this helps.
Select the 2007 records and export the selection as a new table. Delete the 2007 records from the original table. Change the column names in the two tables. Join the new table to the original table on the extent column. If you wish save as a new table.
Pivot Table (Data Management)—ArcGIS Pro | Documentation
Input field - extent
Pivot Field - temporal period
Value Field - count
Using this new table, you can then just add afield and do a field calculation on it such as (year 2007/ year 2020) *100 etc.
I like it. Live and learn. I didn't know data management had a pivot table tool.