How can turn attributes into fields?

592
4
04-22-2022 09:59 AM
JacobDragovich
New Contributor

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).

JacobDragovich_0-1650645109391.png

 

 

0 Kudos
4 Replies
KimGarbade
Occasional Contributor III

I created a Feature Class in a SQL Server enterprise GDB that has this table:

KimGarbade_0-1650654537564.png

The SQL Select statement below will creates a table in my database called BogusStatesFormat that looks like this:

KimGarbade_1-1650654619520.png

 

 

 

 

Select State, [2007], [2020],([2007]-[2020]) as YearDiff
Into BogusStateFormat
 from
 (
   Select State, MyCount, Year
   FROM [TESTSTATES]
  ) src
  pivot
  (
    Sum(MyCount)
	for Year in ([2007],[2020])
  ) 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.

K

0 Kudos
AndyAnderson
Occasional Contributor II

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.

— Andy

0 Kudos
DavidPike
MVP Frequent Contributor

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. 

KimGarbade
Occasional Contributor III

I like it.  Live and learn. I didn't know data management had a pivot table tool.