Transpose rows from a table as attribute fields in a feature class

[IonutAlixandroae]

Hello,

Could someone help me with this issue? I want to transpose all the rows from a table as attribute fields in a feature class and I find it difficult to do for almost 500 tables and 500 rows in the feature class.

So practically I want to avoid doing a relationship class and move the data like this:

Table

------------------------------------------------

Type | Surface | Name |

A     |  11          |  Name1|

B     |  12          | Name 2|

.

.

.

.

n     | n          | Name"n"|

And those multiple rows I want to move in a feature class and the feature class`s attribute table to become like this:

Feature | FC Surface | Type1| Surface1|Name1|Type2|Surface|Name2|..............|Type n|Surface n|Name n|

1          | 1212121      |   A      |    11       | Name1| B      |  12      | Name2|.............| 'n'       | .....          | .........  |

Hope you understand:)

Thanks,

Ionut

[KishorGhatage]

Try using  Transpose tool:

ArcGIS Help (10.2, 10.2.1, and 10.2.2)

[IonutAlixandroae]

I was thinking about that but it doesn`t help me in this situation...

Thanks,

Ionut

[RichardFairhurst]

Are you saying you want to combine 500 tables, 500 rows and at least 3 fields per row into 1 row?  That is 750,000 fields in a single row.  The maximum number of fields you can have in a dbf table or shapefile is 255 fields.  The maximum number of fields you can have in an FGDB table or feature class is 65,534 fields.  So you cannot do what you seem to have described regardless of the tools you use.

Additionally, even if you could do this, once you get beyond about 30 rows transposed, it becomes virtually impossible at a practical level to write any SQL logic against the table or compose a label expression to make selective use of specific fields based on the values they contain.  So even if you can create what you say you want, it may turn out to not work at a practical level in the end, depending on what you intend to do with this feature class/table.

[IonutAlixandroae]

Hi Richard,

Well, I would like to add 60 more fields into that feature class and those 60 fields to be populated with the data from those 500 tables. 500 tables for 500 records in that feature class( each table would have 30 rows and 2 fields that would mean 60 fields per record to be added and populated).

Ionut

[RichardFairhurst]

Well 60 fields is somewhat manageable.  However, I am still not understanding what you are working with.  Ignore the tables for a moment.  What is the feature class like that relates to these 500 tables?  Does it contain 500 features?

If the 500 table contain different field names then you could still end up with a huge number of fields.  Combining all tables together into a single feature class would result in 500 tables * 60 fields each = 30,000 fields.combined.  Do you intend to end up with 500 separate feature classes, or do all 500 table contain the same set of fields?  If they contain the same set of fields, why did you create 500 tables in the first place?

It would help if I had a clue what kind of data this is and a practical example of one table relationship to your feature class.  I.e, There are n features which are agricultural fields and each table contains separate monthly sample data by crop type.  Leaving everything abstract is not helping me even imagine a scenario that would produce the kind of data you are working with.

[IonutAlixandroae]

Let`s say that you have tables regarding CLC ( Corine Land Cover) type, surface and name of the state for each of the USA states and also you have a feature class ( polygon) with 50 records with USA states. You would like to add 1 table information for 1 record in feature class. So practically to be able to find for each state of USA the types of CLC and also the surface of each of those types. Hope it`s not more confusing now...

Also, I do not want to use relations/relationship classes...

Thanks,

Ionut

[RichardFairhurst]

The practical example helps and I was using "table relationship" in the broad database sense to mean what does the data in the feature class have in common with the data in the table.

My confusion now is how do you combine 500 separate tables and end up with less than 500 fields?  I can only image that there is overlap in the table field names.  Or else, you do not really intend to combine all 500 tables together at all, and instead you want to end up with 500 separate feature classes where any given feature class could have up to 60 fields from just one of the 500 tables.

[IonutAlixandroae]

Here`s a schema for better understanging

[VinceAngelo]

The power of databases is harnessed through rows, not tables.  Creating "One table per record in feature class" is an easy way to cripple any database (even 250,000 files in one directory will cripple effective file system operation, and file systems are designed to hold more files than databases are designed to hold tables.). 

I strongly recommend you go back to the design stage and plan something less brutal to effective database operation. The simplest solution is to populate ONE table, organized {ID,Type,Name}, index the table on ID, and use queries that state "SELECT Type,Name FROM table WHERE ID= n"

- V