Pivoting Node - Transpose rows to column

Hi,

I have a table (see example below) which is row based due to the fact that comes out of JSON path,

Duedate Questionid Optiontext Optionid FinalAnswer
2017-01-21T00:00:00.0 Q1.AWARENESS 0-1 year 1 4
2017-01-21T00:00:00.0 Q1.AWARENESS 1 - 3 years 2 4
2017-01-21T00:00:00.0 Q1.AWARENESS 3 - 6 years 3 4
2017-01-21T00:00:00.0 Q1.AWARENESS 6 - 10 years 4 4
2017-01-21T00:00:00.0 Q1.AWARENESS 10 years + 5 4
2017-01-21T00:00:00.0 Q1.AWARENESS Not aware 6 4
2017-01-21T00:00:00.0 Q2.WITH Alone 7 8
2017-01-21T00:00:00.0 Q2.WITH with other 8 8

2017-01-21T00:00:00.0

Q2.WITH I dont want to say 9 8

 

I want to transpose every question in coulmn Question to a new column. I know that should happen with the Pivoting node. But I am not sure which variable I need to put in "Groups" "pivots" "Manual aggregation".

In "Pivots" I put the questionid, and then what I nedd to put in "Groups" and "Agreggation" in order to work as I want.

Ultimately, I would like the output to be the followinng.

 

Duedate Q1.AWARENESS Q2.WITH Optiontext Optionid
2017-01-21T00:00:00.0 4 ? 0-1 year 1
2017-01-21T00:00:00.0 4 ? 1 - 3 years 2
2017-01-21T00:00:00.0 4 ? 3 - 6 years 3
2017-01-21T00:00:00.0 4 ? 6 - 10 years 4
2017-01-21T00:00:00.0 4 ? 10 years + 5
2017-01-21T00:00:00.0 4 ? Not aware 6
2017-01-21T00:00:00.0 ? 8 Alone 7
2017-01-21T00:00:00.0 ? 8 with other 8
2017-01-21T00:00:00.0 ? 8 I dont want to say 9

 

Could you please hlep out in this. Many thanks,.

So I have done the following.

"Groups" -> DueDate, Optiontext

"Pivots"-> questionid

"Manual Aggregation"->FinalAnswer (First)

It created the table as I want. BUT, the problem now is in FinalAnswer(first). I have questions that have multiple response answers and with (First) it take only one answer. and I do not want to create a list of items inside the cell. How can I solve this?

How about you generate a list, and afterwards you ungroup the list? 

Best, Iris