Pivot multiple row values into one column

Hello everyone!
I am new to KNIME but not to data wrangling, so I though it might be a good idea to use the Pivot Node to rearrange my data set:


My plan was to reshape it and have Country, Crop and Location as columns like this:


So I took the Pivot Node, set Key as Pivot, value as list Aggregation.
However, I received Execute failed: Duplicate column name Country
I understand, KNIME wants to transfer ANY value into a separate column but fails because I have duplicates, but I would like them to merge at one column of the same name if they have the same value (e.g. Country).

I searched the forum, but found no one facing this issue, yet. Any thoughs?

Does taking the “Test” column as Group column help?
If I aggregate “First” instead of “List”, the result looks like this for me:

(To get “clean” column names, you should choose in the Advanced settings “Pivot name” for Column name and “Keep original name(s)” for Aggregation name, then use a Column Resorter to change the column order)

1 Like

Seems like the obvious thing to do, yes. :melting_face:
Works like a charm, thanks. I’ll mark this as resolved.

I get that I only capture the first occurence of a key’ value in a group. What if I had more values to a key, like 2 crops for example? Could I split these in 2 rows?

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.