2

I would like to shift data found in a column to a row by Key field. I have a table, within a geodatabase, set up similar to what's below.

KEY_    Value
123     Yellow
123     Blue
456     Red
456     Yellow
789     Green
246     Green
246     Orange

I want to create a table that moves the data in the Value field to rows based on the KEY_ field. The result will look like what's below.

KEY_    Value001    Value002
123     Yellow      Blue
456     Red         Yellow
789     Green
246     Green       Orange

Does anyone have an idea of how this can be done? I tried using the Transpose tool Esri offers but it didn't produce the result I'm looking for. I have basic understanding of Python.

Patty Jula
  • 1,083
  • 2
  • 12
  • 29

2 Answers2

1

I do this in MS Access. Sort of clunky but it works.

123 Yellow

123 Blue

456 Red

456 Yellow

789 Green

246 Green

246 Orange

Summerize the ID's to a new Table, Uniq

123

456

789

246

Add a field to Uniq, named All_Values join Uniq to the original data on ID, this is a one_to_many relationship concatenate all colors per ID

UPDATE uniq INNER JOIN [orig] ON uniq.ID = [orig].ID SET uniq.all_values = uniq.all_values & "," & [orig].color

Export to a CSV or text file, fix commas add a Header for Column names

Maybe it will give you some ideas to build on.

klewis
  • 7,475
  • 17
  • 19
  • Hi, thanks for the response. I worked with my table in Access a bit this morning but haven't made any progress. Access isn't my strength - if you can offer me more step by step instruction that would be great, otherwise I'm going to speak with the Access expert in my office when he's available. – Patty Jula Oct 12 '11 at 17:23
0

You might try PivotTable_management.

blah238
  • 35,793
  • 7
  • 94
  • 195
  • Thanks for the suggestion. The Pivot Table doesn't produce quite the result I am looking for. There isn't a suitable Pivot Field in my table, I just want the data in Value to move to rows by KEY_ field. – Patty Jula Oct 12 '11 at 14:35