This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
on 12-11-201704:05 PM - edited on 05-21-201902:52 PM by SydneyF
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 tool will 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 then a 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!