How To: Use a Cross Tab without having the data rearranged?

[LisaL]

Have you ever wanted to do a Cross Tab, but needed the results in a particular order? You've probably discovered that the Cross Tab toolwill dutifully change your data to column headings and keep the rest of the data correlated, but that the output columns will be arranged in alphabetical order. It's as if there were a Select tool with the "Sort on Field Name" Option thrown into your workflow without you putting it there.

You can manage this situation by preserving the initial order with a Record ID.Use the Record ID to determine the order of the data and thena Dynamic Rename to create the column names. Here's an example that should get you thinking in a productive direction.

If you add a complication to this by grouping data in your Cross Tab, you'll have to rearrange things somewhat. In that case, simply stacking the data isn't sufficient because there are multiple rows, only some of which have the column names in them. One way to handle that situation is to configure the Union tool first using just the column headings, explicitly ordering the Union inputs. Then adding in the rest of the data once the column order is set.You can configure the Union to assemble the columns by name, but you have to match the name exactly. Cross Tab substitutes underscores for punctuation characters, so I added a Formula to mimic that in the example below.

This is only one way to do it. Do you have a better one? Add it to the Comments!

[LisaL]

Alternatively, if you want to group by the names so that they stay in a Name column when you Cross Tab, the workflow can be quite simple.  Take a look at this approach, workflow linked above. (Thank you to @MichelSza for providing this.):

[siddharthdubey]

How about if we want to change the order completely, before writing in an excel sheet?

[LisaL]

@siddharthdubey 

Generally, you can do that with a Select tool.