How to Pivot MySQL Column Data into Rows Using MAX() and CASE Statements?

Displaying MySQL Pivot Table Column Data as Rows

Converting a table's column data into rows can be a challenging task, especially for multi-dimensional datasets. In MySQL, this operation is commonly known as "pivoting."

Problem:

The provided dataset consists of three tables:

• Question: Stores question titles.
• Results: Records user responses to specific job IDs.
• Answers: Contains the answers for each question and result combination.

The goal is to restructure the data to exhibit the question answers as columns for each result set.

Solution:

To achieve this, we can utilize a MySQL query that leverages the MAX() aggregate function in conjunction with CASE statements. The query below effectively pivots the column data into rows:

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:

• The query selects various fields from the Results (a) table, including the result ID, user ID, and job ID.
• It then uses an INNER JOIN to retrieve the corresponding answers from the Answers (b) table.
• Another INNER JOIN links to the Question (c) table to identify the actual question for each answer.
• The MAX() function is applied within CASE statements to aggregate the answer for each result and question combination.
• Finally, the results are grouped by result ID, user ID, and job ID to create the desired row structure.

This query yields the following output:

result_id	user_id	job_id	Is it this?	Or this?	Or that?
1	1	1	Yes	No	Maybe
2	1	3	Maybe	No	Maybe
3	2	3	Yes	Yes	No

Dynamic Version:

If you have an unknown number of questions (e.g., 1000), a dynamic version of the query is more suitable. This can be achieved using dynamic SQL and variable concatenation:

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 dynamic query uses variable concatenation to build the SQL statement dynamically based on the contents of the Question table. The result is a more flexible query that can handle any number of questions.

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