Here is how I would approach it:
I would split both name and id fields using a Custom Split
You can see that that gives you Split 1, Split 2, etc... that matches the position in the string:
Go ahead and remove the id and name fields after you've split them as you won't need them anymore.
Then, you can do a coordinated pivot on each of the split fields:
You're really close, but you'll also want to filter out the NULL/blank values and probably use an aggregate to remove any duplicates:
I've attached a packaged flow files (.tlfx) so you can dig into the details!
Hope that helps!
split.tflx 4.5 KB