Cross Tab Concatenate Help Needed

[kubikbr]

Looking for some assistance from the community on a cross tab and concatenate. Table below is my original source data. It consists of a long list of patients and each has a corresponding TOS code. As you will see some patients have multiple codes. Looking to concatenate these unique codes all into one column that can then be joined to my dataset, which is the 2nd image below. Workbook attached if its helpful. 

Original data:

Patient ID	TOS
Patient 1	98
Patient 2	94
Patient 2	95
Patient 3	96
Patient 3	95
Patient 3	93
Patient 3	92
Patient 4	92
Patient 4	95

Desired Outcome:

Patient ID	TOS
Patient 1	98
Patient 2	94, 95
Patient 3	96, 95, 93, 92
Patient 4	92, 95, 96, 97

[ShankerV · Accepted solution]

Hi @kubikbr 

The summarize tool will help for this use case.

 

Step 1: 

Step 2:

 

[alexnajm]

Summarize tool is your option here!

Edit: workflow included, and same idea @ShankerV !

[apathetichell]

If you have a restriction on using summarize tool because of "stuff" - you could use a crosstab. You'd have to a) convert your TOS field to string b) use a formula tool to create  a new column (let's call it new_column_header) and give it a value of "TOS" 3) Cross tab (with Patient ID as key, new_column_header as column name and TOS as value. set mode to concatenate.