Grouped pivot from rows

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

Workflow

Datasets

Reporting

Tips and Tricks

Preparation

Data Investigation

Accepted answers

DataNath

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.

Transform primary and secondary states.yxmd

DataNath

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!

All comments

DataNath

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.

Transform primary and secondary states.yxmd

binu_acs

@knobsdog One way of doing this

pivot_1018.yxmd

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

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703