Pivoting data from repeating group columns to single column group

Hello

One issue I get with my survey data, that they produce lots of columns that hold same type of data, but in repeating groups of columns.

columnNames: A1,B1,C1,A2,B2,C2,A43,B43,C43
Responses: Name1,IDcode1,Answer1,Name2,IDcode2,Answer2,Name43,IDcode43,Answer43

How do I put them into single column group?

ColumnNames: A,B,C
Response1: Name1,IDcode1,Answer1
Response2: Name2,IDcode2,Answer2
Response3: Name43,Idcode43,Answer43

Any help would be much appreciated!

Thank you,

Milly

Close to what you need could be done by Unpivot node.

Yes, but doesn’t that just put all of the unpivoted columns into single column? I need to make sure the data groups remain on same row in different columns, so I can’t unpivot one column at a time (plus, there might be over a hundred columns that would mean to make over 100 unpivot nodes…)

  1. Unpivot
  2. String Replacer or Rule Engine to extract the new column names from the old ones
  3. Pivot

Thanks, it worked!