How to Pivot MySQL Table Data with Question Answers as Columns?

MySQL Pivot Table Column Data as Rows

Pivot tables are a powerful tool for transforming data from a column-based format to a row-based format. In MySQL, this transformation can be achieved using the CASE expression in conjunction with aggregation functions.

To convert the given data into a pivot table format, where question answers are displayed as columns, the following query can be used:

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

In this query, the CASE expression is used to check the question column of the Question table and return the corresponding answer from the Answers table. The aggregation function MAX is then used to find the maximum value for each question, which represents the answer provided for that question.

Dynamic Pivoting

For cases where the number of questions is unknown or large, a dynamic SQL statement can be generated using the following code:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN c.question = ''',
      question,
      ''' then b.answer end) AS ',
      CONCAT('`',question,'`')
    )
  ) INTO @sql
FROM Question;

SET @sql = CONCAT('SELECT  a.ID,
                            a.user_ID,
                            a.job_id, ', @sql, ' 
                    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');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This code generates a dynamic SQL statement that can handle any number of questions. It dynamically constructs the SELECT clause by concatenating the maximum value for each question as a column name.

The result of the query will be a pivot table with the desired format, where each question answer is displayed as a separate column.

The above is the detailed content of How to Pivot MySQL Table Data with Question Answers as Columns?. For more information, please follow other related articles on the PHP Chinese website!