Mysql Covert rows to columns

Question

I have a table with order numbers, first name, last name, question and answers. There are 5 questions asked to the user, each answer to a question generates 1 row of data, which produces 5 rows per user. I need a query that returns order number, first name, last name and the questions and answers converted to columns, returning 1 row per user.

Any help would be appreciated

Thanks, Larry

Answer 1

Seems like you want to join the table to itself 5 times.

Something like

select q1.first_name, q1.last_name, max(q1.question), max(q1.answer), max(q2.question), max(q2.answer),max(q3.question), max(q3.answer),...
from questions q1 
join questions q2 on q1.first_name=q2.first_name and q1.last_name=q2.last_name 
join questions q3 on q1.first_name=q3.first_name and q1.last_name=q3.last_name 
where q1.order_number = 1 and q2.order_number = 2 and q3.order_number = 3 ...
group by q1.first_name, q1.last_name

Using max will collapse down the rows into unique first name/last name pairs.