Pivoting multiple columns with the same column values as its values

Arthi Annadi

I have a scenario where I have rows from multiple columns that I want to pivot into a multiple columns. Let's say I have three columns: 'Questions,' 'Labels,' and 'Values.' I tried pivoting the 'Questions' column into separate columns with values from the 'Values' column.

Now, I also want to pivot the 'Labels' column into separate columns, and the column should have a value from the 'Values' column.

I am unable to add two columns to pivot at once; it only allows me to select one column to choose the labels I want.

How can I achieve this?

ArborRose

The way you describe it, you are pivoting on the values in column, "Values". After that pivot, you would have Questions, Labels, and new columns, based on "Values", but not the values column itself. I would think if you want to pivot twice off the same column "Values", you would have to duplicate the column before the pivots.

I think you have to try it in multiple steps. Start by pivoting the first column. This will create a new dataset with the first column pivoted. Which you would configure and save. Then pivot again.

Let's assume you have a dataset with columns: Date, Category, Metric, and Value.

Date	Category	Metric1	Metric2	Value
2023-07-01	A	X	Alpha	10
2023-07-01	B	X	Beta	15
2023-07-02	A	Y	Alpha	20
2023-07-02	B	Y	Beta	25

Our goal is to pivot both Metric1 and Metric2 columns.

Intermediate Dataset (after Pivoting Metric1)

Date	Category	Metric2	X	Y
2023-07-01	A	Alpha	10
2023-07-01	B	Beta	15
2023-07-02	A	Alpha		20
2023-07-02	B	Beta		25

Final Dataset (after Pivoting Metric2)

Date	Category	Metric1	Alpha	Beta
2023-07-01	A	X	10
2023-07-01	B	X		15
2023-07-02	A	Y	20
2023-07-02	B	Y		25

Arthi Annadi

@ArborRose

Thank you so much for your response. However, in the above example, I see only one metric being pivoted at a time. I am trying to pivot two metrics simultaneously. I tried creating a duplicate dataset through SQL transform, created two separate pivots for each metric, and then joined the two pivoted datasets.

However, I observed that my pivot does not include multiple rows for a single identified row. For example, if the single identified row is 'Submission ID' and there are multiple values for a single submission ID in the pivoted columns, it displays only one response.