rows to columns - multiple rows into a single row

[agumaste]

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 ID	source_patient_id	case_id	referral_date	case_status_date	case_substatus_reasons_code	case_substatus_reasons_description
10014	58	25	5/12/2023	5/12/2023	CN	HCP DECISION
10014	58	528	7/5/2023	7/27/2023	AC	APPROVED ON THERAPY

And I want to convert this into:

Patient ID	source_patient_id	referral_date_1	referr_date_2	case_status_date_1	case_status_date_2	Status_reason_code_1	Status_reason_code_2	diff_ref_date	final_disposition
10014	58	5/12/2023	7/5/2023	5/12/2023	7/27/2023	CN	AC	54	APPROVED 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?

Thanks! 

[Peachyco]

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:

 

 

My starting dataset:

My final dataset:

[binuacs · Accepted solution]

@agumaste One way of doing this

[flying008 · Accepted solution]

Hi, @agumaste 

Another way like @binuacs :