I currently have a solution, but I am hoping that someone has a better way than just a lot of "Select" tools, renaming fields, and then a "Union" tool.
I have a dataset that generally looks like this:
Month | Day | 1Bill | 1To | 1From | 1Time | 1B | 1P | 2Bill | 2To | 2From | 2Time | 2B | 2P | 3Bill | 3To | 3From | 3Time | 3B | 3P |
5 | 10 | ABC | LAX | JFK | 5 | 1 | 0 | ABC | SEA | LAX | 3 | 1 | 0 | ABC | SFO | SEA | 3 | 1 | 0 |
5 | 12 | DEF | SFO | SEA | 3 | 1 | 0 | DEF | DEN | SFO | 3 | 1 | 0 | EFG | ORD | DEN | 3 | 0 | 1 |
And I would like to get it into a dataset that looks like this:
Month | Day | Bill | To | From | Time | B | P |
5 | 10 | ABC | LAX | JFK | 5 | 1 | 0 |
5 | 10 | ABC | SEA | LAX | 3 | 1 | 0 |
5 | 10 | ABC | SFO | SEA | 3 | 1 | 0 |
5 | 12 | DEF | SFO | SEA | 3 | 1 | 0 |
5 | 12 | DEF | DEN | SFO | 3 | 1 | 0 |
5 | 12 | EFG | ORD | DEN | 3 | 0 | 1 |
@KyleF I started out with the Transpose tool to flip the data and put the future field names into one field where I could use the RegEx tool to parse out the number as the first character and turn it into an ID field and the rest into a field named Field. Then I used the Cross Tab tool to pivot it back while grouping data on Month, Day, and ID.
@KyleF Another way of doing this