Join the Alteryx Community’s Maveryx Summer Cup event! Compete, network with others, and earn your gold through a series of challenges from July 24th to August 11th. Learn more about the event here.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

rows to columns - multiple rows into a single row

5 - Atom

Hi, I am trying to convert multiple rows into a single with converting some of the elements of a transaction into columns. The data I have is as follows:


Patient IDsource_patient_idcase_idreferral_datecase_status_datecase_substatus_reasons_codecase_substatus_reasons_description
1001458255/12/20235/12/2023CNHCP DECISION
10014585287/5/20237/27/2023ACAPPROVED ON THERAPY


And I want to convert this into:


Patient IDsource_patient_idreferral_date_1referr_date_2case_status_date_1case_status_date_2Status_reason_code_1Status_reason_code_2diff_ref_datefinal_disposition
10014585/12/20237/5/20235/12/20237/27/2023CNAC54APPROVED ON THERAPY


I have total 30 unique Ids for which I need to do this. Only for one Id I have three transactions, rest all I have two transactions per ID. 


Could you please advise on how I can achieve this?



11 - Bolide

I would use the Multi-Row Formula tool to assign row ID/counts to each patient - this would allow me to tag the numeric prefixes to the cross-tabbed columns. Then, I'd use Cross Tab and Summarize tools, all of them grouping by Patient ID and Source Patient ID so I can join all of them together in the end. Like so:

Alteryx - agumaste rows to columns a.png

My starting dataset:

Alteryx - agumaste rows to columns b.png


My final dataset:

Alteryx - agumaste rows to columns c.png


20 - Arcturus

@agumaste One way of doing this


14 - Magnetar

Hi, @agumaste 


Another way like @binuacs :