Pivoting multiple columns into 1 Row

[mlay]

Hi, I've come across a problem I don't know how to solve.

I need to pivot multiple rows into multiple columns like the screenshot below, grouped by the business_ID (business_ID appears once with all personal details as new columns)

- The problem is a business ID can have a dynamic number of firstname/surname/address details - as per the data file 

- I'm not sure how to correctly pivot the data into 1 row per business_ID with all the required personal details of each person within the business

Any help appreciated. Thanks!

Michael

[caltang]

Michael, doing this will likely crash your Alteryx if you have >10000 columns. The number of columns you’re going to make is going to be a “crazy” amount to process. Why do you need it this way if I may ask?

[mlay]

Calvin, it won't. I'm not really sure how to respond to a superfluous amount like "crazy". Do you have any solutions?

[caltang]

Just so I understand you correctly, you are not trying to make a column for each name right? If not, I misread your dataset. 

Using your data, can you show your desired output with just 2 IDs for now?

[caltang]

@mlay I understand you better now. Is this what you want?

 

[caltang]

My apologies @mlay , I cannot send you the workflow but I can send you screenshots.

 

  

 

 

  

 

 

  

[KGT · Accepted solution]

One of the issues that you face here is trying to get the fields in the right order. This is not a normal operation as the data is then not in a good format for further work. However, sometimes a report needs to look like this, just need to put lineID's on to be able to sort it well.

• Add a LineID grouped by Business ID which will effectively give you a recordID per Business_id.
• Transpose everything except Business_id and LineID,
• Add another ID that will indicate the order of your fields (I would normally do this as a separate stream to prepare the names exactly, but in-line here).
• Create the header.
• Cross-tab back.
• Finally, use a Dynamic Rename to rename the headers back to what they should be.

[caltang · Accepted solution]

Managed to recreate and store it. 

My solution is longer, and orders the column name alphabetically. If you have a specific order in mind, you can tweak that portion in the formulas (5) and (18) in the workflow.

Though @KGT 's solution is more minimalistic, it gets the job done as well. 

[mlay]

Thank you Calvin and KGT for your efforts, this is exactly what I need! 

Much appreciated :)