Comment faire pivoter les données des colonnes MySQL en lignes : une approche de requête SQL ?

Pivoter les données de colonnes MySQL sous forme de lignes

Lorsque vous travaillez avec MySQL, une tâche courante consiste à transformer les données d'un format de colonne en un format de ligne . Ce processus, appelé pivotement, peut être particulièrement utile pour présenter les données de manière plus conviviale et plus digeste.

Considérez le scénario suivant :

Vous disposez de trois tableaux :

• Tableau des questions (id, question)
• Tableau des résultats (id, user_id, job_id)
• Tableau des réponses (id, réponse, fk_question_id, fk_result_id)

Le tableau des questions contient une liste de questions possibles. Le tableau Résultats associe les utilisateurs aux résultats de tâches. Le tableau Réponses stocke les réponses des utilisateurs aux questions pour chaque résultat.

Votre objectif est d'afficher les réponses aux questions sous forme de colonnes pour chaque ensemble de résultats. Le résultat souhaité est le suivant :

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

Pour réaliser cette transformation, vous pouvez utiliser la requête MySQL suivante :

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

Cette requête utilise une combinaison d'instructions INNER JOIN et MAX( ) et les fonctions CASE pour regrouper les réponses pour chaque combinaison de questions et de résultats. Il fait pivoter efficacement les données d'un format de colonne à un format de ligne, vous permettant de visualiser les réponses sous forme de colonnes.

Pour exécuter cette requête, remplacez les noms de table et de colonne dans les clauses FROM et JOIN par votre table réelle. et les noms de colonnes. Une fois exécutée, la requête renverra les données pivotées dans le format souhaité.

Alternativement, si vous avez un grand nombre de questions, vous pouvez utiliser une version SQL dynamique de la requête pour traiter un nombre inconnu de questions. Cette approche dynamique est particulièrement utile lorsque le nombre de colonnes dans le résultat pivoté est inconnu ou change fréquemment. La version SQL dynamique est la suivante :

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;

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!