Transpose

[ksab23]

Hi All, 

I would need some help with transposing data and for the life of me, I cannot figure it out. The dataset sample is attached. I need to transpose questions of the survey, answers, and scores (optional) into columns -> question text 'columns'+question answers 'rows' (there is also a score column but that one can be discarded).

Course Title	Course ID	Question Text	Question Response	Person Full Name	Person E-mail	Evaluation Submitted On	Score
		I am likely to change my behavior following this training.	Agree			2023-04-27	4
		The design and duration was appropriate for the topic.	Strongly Agree			2023-05-09	5
		The design and duration was appropriate for the topic.	Strongly Agree			2023-03-09	5
		I would recommend this course for someone in my role.	Strongly Agree			2023-05-09	5
		I am likely to change my behavior following this training.	Neither agree nor disagree			2023-03-16	3
		I am likely to change my behavior following this training.	Strongly Agree			2023-03-10	5
		The design and duration was appropriate for the topic.	Neither agree nor disagree			2023-03-28	3
		Please explain your ratings and make suggestions for future training.	N/A			2023-03-30
		The design and duration was appropriate for the topic.				2023-04-12
		I would recommend this course for someone in my role.	Neither agree nor disagree			2023-03-28	3
		The design and duration was appropriate for the topic.	Agree			2023-03-16	4
		Please explain your ratings and make suggestions for future training.	None			2023-05-04
		I am likely to change my behavior following this training.	Agree			2023-05-12	4
		The design and duration was appropriate for the topic.	Strongly Agree			2023-03-09	5
		The design and duration was appropriate for the topic.	Strongly Agree			2023-03-30	5
		The design and duration was appropriate for the topic.	Agree			2023-05-12	4
		I am likely to change my behavior following this training.	Strongly Agree			2023-03-30	5
		I am likely to change my behavior following this training.	Neither agree nor disagree			2023-03-28	3
		Please explain your ratings and make suggestions for future training.	N/A			2023-03-09

I deleted sensitive information from the dataset, however as the data is now, for each answer you have the course user took and score per question (not per course+questions, which is as stupid as it gets, as I cannot even get an avg score per course this way).

Ideal outcome (plus the columns I omitted, email, course name, etc.):

User	Course	Question1	Question2	Question3
X	Y1	answer	answer	answer
X	Y2	answer	answer	answer
X1	Y1	answer	answer	answer

Thanks a lot!

[ryan_mayu]

@ksab23 

pls see the attachment below.

[ksab23]

exactly what I need, how did you do it please?

[ryan_mayu · Accepted solution]

@ksab23 

select quesion text column and click pivot column

choose queston column and  don't aggregate

[Anonymous]

I dont understand your expected output related to your question but, you should maybe add Pivots/ Unpivots  then transpose

[ksab23]

Hi, my expected outcome is this (below) and I cannot make it work really with pivot/unpivot and transpose. No matter what combination I try, it always gives me a messed-up result. So I know what to use but not exactly how if that makes any sense

name	email	course Title	Course ID	date	I am likely to change my behavior following this training.	The design and duration was appropriate for the topic.	I would recommend this course for someone in my role.
					Agree	Agree	Strongly Agree
					Strongly Agree	Strongly Agree	Strongly Agree