Dynamic Rename using Column Values as Prefix + Crosstab

[kwilda]

I have seen examples of using a Column value to update a single new column name, but not multiples.

Looking to pivot each instance of PLANTYPE to a prefix for the 3 associated columns
So the resulting Columns would be DENTAL_CANCELREASON, DENTAL_CANCELDATE, DENTAL_CANCELYEAR
This would repeat for each PLANTYPE
With 40+ PLANTYPE values, this would result in 120+ columns, most world be null() 
resulting data needs to be grouped by the ID field, pivoting to 1 row per ID. Any help is appreciated

ID PLANTYPE  CANCELREASON           CANCELDATE CANCELYEAR
1   DENTAL       Family Needs Changed  12/31/2020        2020
1   TERM_C2    Family Needs Changed  1/1/2024            2024

ID DENTAL_CANCELREASON   DENTAL_CANCELDATE DENTAL_CANCELYEAR TERM_C2_CANCELREASON 
1   Family Needs Changed           12/31/2020                       2020                                 Family Needs Changed

TERM_C2_CANCELDATE TERM_C2_CANCELYEAR

1/1/2024                               2024

[martinson]

Dear kwilda I hope you're doing well.

I have a solution using the transpose and cross tab tools. This will work for larger data sets and should be dynamic.

Let me know if you have any other questions or issues. 

martinson

Bulien

Set up of the record ID

Set up of the transpose tool

Set up of the Cross Tab tool.

[Carolyn · Accepted solution]

Hey! Give this a try!

1. Ignore the bits in the container - I was a bit lazy with my copy/paste and then had to parse the data into columns. Your data wouldn't be like that so you wouldn't have to do it :)
2. Transpose, with Key Columns = ID & Plan Type
3. Formula Tool to Concatenate the PlanType & Name columns
4. Cross tab to put it back together, grouped by ID

[kwilda]

Many thanks Carolyn! I had not considered the transpose, instead fixating on using the dynamic rename.

I greatly appreciate the effort