How to Pivot MySQL Column Data into Rows: A SQL Query Approach?

Pivoting MySQL Column Data as Rows

When working with MySQL, a common task is to transform data from a column format into a row format. This process, known as pivoting, can be particularly useful for presenting data in a more user-friendly and easily digestible manner.

Consider the following scenario:

You have three tables:

• Question table (id, question)
• Results table (id, user_id, job_id)
• Answers table (id, answer, fk_question_id, fk_result_id)

The Questions table contains a list of possible questions. The Results table associates users with job results. The Answers table stores user responses to the questions for each result.

Your goal is to display the question answers as columns for each result set. The desired outcome is as follows:

+-----------+---------+--------+-------------+----------+----------+
| 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       |
+-----------+---------+--------+-------------+----------+----------+

To achieve this transformation, you can utilize the following MySQL 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

This query employs a combination of INNER JOIN statements and MAX() and CASE functions to aggregate the answers for each question and result combination. It effectively pivots the data from a column format to a row format, allowing you to visualize the answers as columns.

To execute this query, replace the table and column names in the FROM and JOIN clauses with your actual table and column names. Once executed, the query will return the pivoted data in the desired format.

Alternatively, if you have a large number of questions, you can use a dynamic SQL version of the query to handle an unknown number of questions. This dynamic approach is particularly useful when the number of columns in the pivoted result is unknown or changes frequently. The dynamic SQL version is as follows:

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;

The above is the detailed content of How to Pivot MySQL Column Data into Rows: A SQL Query Approach?. For more information, please follow other related articles on the PHP Chinese website!