I'll try that. But once I transpose, I'll get the following table (below).
I'm looking to create the following fields from this table.
(1) United States, IndustryId = 1 for all years as GDP total_US
(2) United States, IndustryId=2 for all years as Private Industries_US
(3) United States, IndustryId=3 for all years as Agriculture, forestry, fishing and hunting_US
(4) Abilene, Tx, IndustryId = 1 for all years as GDP total_Abilene
(5) Abilene, Tx, IndustryId=2 for all years as Private Industries_Abilene
(6) Abilene, Tx, IndustryId=3 for all years as Agriculture, forestry, fishing and hunting_Abilene
You will have to create a unique key by concatenation of industry ID and Geo name in the required format that you have mentioned above. Then load that field from your load script and use the same in the table.
Similarly for any other data display if u have duplicates you can just create a unique key by concatenation and work on that data.
Hope this helps.