Hi,
I’m struggling with a basic operation (I am still discovering Knime )

I have the following table

User

Answer

User 1

User 1_Answer 1

User 2

User 2_Answer 1

User 3

User 3_Answer 1

User 4

User 4_Answer 1

User 5

User 5_Answer 1

User 1

User 1_Answer 2

User 2

User 2_Answer 2

User 3

User 3_Answer 2

User 4

User 4_Answer 2

User 5

User 5_Answer 2

…

…

And I’d like to pivot the table like this:

User

Answer 1

Answer 2

User 1

User 1_Answer 1

User 1_Answer 2

User 2

User 2_Answer 1

User 2_Answer 2

User 3

User 3_Answer 1

User 3_Answer 2

User 4

User 4_Answer 1

User 4_Answer 2

User 5

User 5_Answer 1

User 5_Answer 2

My first idea would be to add a “Group” column to the table and then pivot it:

User

Answer

Group

User 1

User 1_Answer 1

1

User 2

User 2_Answer 1

1

User 3

User 3_Answer 1

1

User 4

User 4_Answer 1

1

User 5

User 5_Answer 1

1

User 1

User 1_Answer 2

2

User 2

User 2_Answer 2

2

User 3

User 3_Answer 2

2

User 4

User 4_Answer 2

2

User 5

User 5_Answer 2

2

…

…

…

But even adding the Group column is not so easy (at least for me ^^).
I tried to increase the Group value every time we “reach” a “User 1” row but did not find a proper way to do it.

All in all, I suspect there would be a much more efficient way to go from the initial state of my table to the expected output.

Hi @pierrelouis ,
I have thought of two methods. Personally, I suggest using the first one. The idea is: 1. Group by users, but put the answers in a List. 2. Expand the List in the column direction.
Among them, I added two more rows to the test data.

I applied the first method and, I agree, it seems the most “elegant” to me.
Aggregating answers in a list before splitting back such a list into columns is a nice trick
(but it’s true that aggregating into list is not the first function most of us think of I guess ^^)

I see - so you can still group by User column and pivot by answer column - what you are then missing is a third column to aggregate on What do you want to see in the rows of your 0 and 1 columns? User 1, User 2 etc?

In that case you can just replicate the User Column (e.g. String Manipulation Node or Column Expressions Node) and then use that new column as aggregation column…