How to Pivot MySQL Column Data into Rows Using Aggregation and Conditional Statements?

MySQL Pivot Table Column Data as Rows

To effectively pivot table column data into rows in MySQL, you can utilize a combination of aggregation functions and conditional statements.

Query:

SELECT
  a.ID,
  a.user_ID,
  a.job_id,
  MAX(CASE WHEN c.question = 'Is it this?' THEN b.answer END) 'Is it this?',
  MAX(CASE WHEN c.question = 'Or this?' THEN b.answer END) 'Or this?',
  MAX(CASE WHEN c.question = 'Or that? ' THEN b.answer END) 'Or that? '
FROM
  Results a
INNER JOIN
  Answers b ON a.id = b.fk_result_id
INNER JOIN
  Question c ON b.fk_question_id = c.ID
GROUP BY
  a.ID,
  a.user_ID,
  a.job_id;

Explanation:

1. Join the Results, Answers, and Question tables.
2. Use the CASE statement to evaluate whether each question condition is met and return the corresponding answer.
3. Aggregate the maximum answer for each question using the MAX() function.

Additional Tips:

• If you have a dynamic number of questions, consider using a dynamic SQL query based on the available questions.
• Use the SQLFiddle demo link provided in the answer for a practical demonstration.

The above is the detailed content of How to Pivot MySQL Column Data into Rows Using Aggregation and Conditional Statements?. For more information, please follow other related articles on the PHP Chinese website!