pivot table help

Question

I am using student edition of ArcGIS 9.3  in arctoolx does not contain pivot tables function. I was wondering if someone could help construct a model builder with excel data using  so that i can link it to shape file  the pivot table. found out my university has it. but because of the up comming thanksgiving breaks the GIS Lab will be closed for maintance. any help one give me is appreciated....... All the stuff been reading and looking at  has lead me to conlusion this probably my only way to preced further in joining to my shapefile

stevekadle · Answer

I will take look at the tonight

stevekadle · Answer

thanks I appreciate the help very much with this help and for looking at for me.

RichardFairhurst · Answer

I tested your data with the Pivot tool in Excel and found that the behavior in Excel is different from the behavior of the ArcGIS Pivot Tool.  In Excel 2007 you can only have numeric values fill in the summary data area defined by the column and row values, but with the ArcGIS tool you can have numeric or string values in the summary data area.  So actually the ArcGIS Pivot Table tool is what you need.

I exported your data to a CVS file from Excel.  I had to rename the field called 2nd_FamilyName to SecondFamilyName because ArcMap does not like fields that begin with numbers.  I opened the CVS file in ArcMap and exported that to a File Geodatabase table.  In the new table I added a new field called CONCAT where I concatenated the various fields (except for Block and Number) into a single string value.  Then I ran the Pivot Table tool and used Block as the row value and Number as the column value and CONCAT as the data values.  I did not preorganize the Number field values with a sort operation so that the correct column order was generated, but that should not be a problem for a label expression and you can drag the columns where you want in a tableview or in Excel.  The result is attached as a Zipped Text file that you should be able to open in ArcMap.  There are still a few blocks with two rows, because of there being two individuals in the same Number plot.  The Excel Pivot can reveal which block groups have two individuals in the same plot number from your original data and you then could edit the table to collapse those records, either by adding more fields or concatenating the two individuals in the same Block and Number to one row and column.

With this data you should be able to create a stacked label.  The ArcGIS Pivot Table tool can be used to output mutiple variations of the table depending on the summary you wanted.  For example it could have just used the FamilyName fields for the data values. However, multiple fields need to be concatenated into a single field prior to running the tool.  Anyway, let me know if these results are basically what you needed and if you want other summary value variations.

Burial_Pivot.zip