Grouped pivot from rows

[knobsdog]

I must be losing my mind but I can't seem to figure this out.  I have the below issue with a workflow, unfortunately I can't share the workflow due to data security but here's a generic dataset.  I've tried a multi row formula, summarize and cross tab tools but I'm clearly missing something.

Current Output

ID	Primary State	Secondary State
78	Georgia	New Jersey
78	Georgia	Arizona
78	Georgia	Pennsylvania

Expected Output	ID	Primary State	Secondary State 1	Secondary State 2	Secondary State 3
78	Georgia	New Jersey	Arizona	Pennsylvania

[DataNath · Accepted solution]

Is this what you're after @knobsdog? It just relies on creating a grouped RecordID within each group which you can then make part of the new headers before Cross-tabbing the results:

This will be fully dynamic to any number of secondary states.

[binuacs]

@knobsdog One way of doing this

[knobsdog]

Man that was really fast.  Can you show the options selected for the Cross Tab and Transpose, they didn't come over in the download.  That is the output I'm looking for just not sure which options to use to get there.  Thank you for your help.

[DataNath · Accepted solution]

No problem at all @knobsdog, can you show what the download looks like? Seems odd that you cant see configurations etc. Anyway, Transpose:

Cross-Tab:

Note on the cross-tab that our [New Header] field will prevent any weird behaviour from the forced aggregation and so we can select any of these options, I always just use concatenate as it's first!

[knobsdog]

It brought in the workflow but had cleared out the options in those two tools for some reason.  Not sure if it's a version difference thing but that helped.  I'm able to get to the output I needed because of this.  Thank you so much for your quick help.