Change orientation of Question Answer Data

[tjamal1]

Hello,

I have a table that has question answers like this

QuestionID	Title	AnswerID	Answer
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	1	2 Points
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	2	3 Points
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	3	4  Points
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	4	5  Points
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	5	6  Points
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	6	7  Points
2	Which of these documents are not needed in your vehicle at all times?	7	Your license
2	Which of these documents are not needed in your vehicle at all times?	8	Proof of insurance
2	Which of these documents are not needed in your vehicle at all times?	9	Registration papers.
2	Which of these documents are not needed in your vehicle at all times?	10	Vehicle Instruction manual

I want to transform it to this form so that each question and its answers exist in one line/record.

QuestionID	Title	Answer 1	Answer 2	Answer 3	Answer 1	Answer 4	Answer 5
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	2 Points	3 Points	4  Points	5  Points	6  Points	7  Points
2	Which of these documents are not needed in your vehicle at all times?	Your license	Proof of insurance	Registration papers.	Vehicle Instruction manual

How can I do this in Alteryx Designer?

[AngelosPachis]

Hi @tjamal1 ,

Here's how you can do it with a cross tab tool; the trick is to count how many columns you have per question, which you can do with a multi-row formula tool

Cheers,

Angelos

[tjamal1]

Thank you @AngelosPachis, that worked perfectly fine.

I have a question my original table has one field which flags answers as True or False. Is there any way to add another column in the last (to your solution) so that Correct Answer will be in the last column called "Correct Answer".

Example

Original Table

QuestionID	Title	AnswerID	Answer	IsCorrect
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	1	2 Points	0
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	2	3 Points	1
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	3	4  Points	0
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	4	5  Points	0
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	5	6  Points	0
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	6	7  Points	0

Expected Table

QuestionID	Title	Answer1	Answer2	Answer3	Answer4	Answer5	Answer6	Correct Answer
1	How many demerit points are awarded for driving when on a hand-held device such as a mobile phone?	2 Points	3 Points	4  Points	5  Points	6  Points	7  Points	3 Points

[AngelosPachis · Accepted solution]

Hey @tjamal1 ,

You can do that by filtering out all the correct answers and joining back to the main table