How to Pivot MySQL Table Column Data into Rows?-Mysql Tutorial-php.cn

How to Pivot MySQL Table Column Data into Rows?

Barbara Streisand

Release： 2024-12-25 19:37:10

Original

974 people have browsed it

How to Pivot MySQL Table Column Data into Rows?

MySQL Pivot Table Column Data as Rows

To pivot table column data as rows in MySQL, you can use a combination of the MAX() and CASE functions along with a GROUP BY clause. Here's the general syntax:

SELECT
  GROUP_BY_COLUMNS,
  MAX(CASE WHEN condition1 THEN column1 END) AS column1_alias,
  MAX(CASE WHEN condition2 THEN column2 END) AS column2_alias,
  ...
FROM TABLE
GROUP BY GROUP_BY_COLUMNS

Copy after login

In the provided example, you have three tables: Question, which contains the questionnaire questions; Results, which links users to jobs; and Answers, which contains the user responses to the questions.

To pivot the question answers as columns for each result set, you can use the following 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

Copy after login

This query combines the Answers table with the Question table based on the fk_question_id field and the Results table based on the fk_result_id field. It then groups the results by the ID, user_ID, and job_id columns, and for each group, it calculates the maximum answer for each question using the MAX() function and the CASE statement.

Alternatively, if you have an unknown number of questions, you can use a dynamic SQL query that constructs the query string based on the available questions:

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;

Copy after login

The dynamic query constructs the @sql variable with the necessary columns for each question and executes it as a prepared statement.

These queries will return the question answers as columns for each result set, allowing you to display them in a user-friendly format.

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