Pivoting multiple columns into 1 Row

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

MOCK_DATA.xlsx

Datasets

Tips and Tricks

Help

Accepted answers

KGT

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.

Screenshot 2025-09-03 185336.png

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.

Multiple users per business in one line.yxmd

caltang

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.

ForMichael.yxzp

All comments

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

Screenshot 2025-09-03 185336.png

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.

Multiple users per business in one line.yxmd

caltang

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.

ForMichael.yxzp

mlay

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

Much appreciated

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703