Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

You might try PivotTable_management.

share|improve this answer
    
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
add comment

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.