Comment faire pivoter les données d'une colonne MySQL en lignes à l'aide des instructions MAX() et CASE ?

Afficher les données des colonnes d'un tableau croisé dynamique MySQL sous forme de lignes

Convertir les données des colonnes d'un tableau en lignes peut être une tâche difficile, en particulier pour les données multidimensionnelles. ensembles de données. Dans MySQL, cette opération est communément appelée « pivotement ».

Problème :

L'ensemble de données fourni se compose de trois tables :

• Question :Question des magasins titres.
• Résultats : Enregistre les réponses des utilisateurs à des ID de travail spécifiques.
• Réponses : Contient les réponses pour chaque combinaison de questions et de résultats.

L'objectif est de restructurer les données pour afficher les réponses aux questions sous forme de colonnes pour chaque résultat set.

Solution :

Pour y parvenir, nous pouvons utiliser une requête MySQL qui exploite la fonction d'agrégation MAX() en conjonction avec les instructions CASE. La requête ci-dessous fait pivoter efficacement les données de la colonne en lignes :

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

Explication :

• La requête sélectionne différents champs de la table Résultats (a), y compris l'ID de résultat, l'ID d'utilisateur et l'ID de travail.
• Il utilise ensuite un INNER JOIN pour récupérer les réponses correspondantes des réponses (b) table.
• Un autre INNER JOIN renvoie au tableau Question (c) pour identifier la question réelle pour chaque réponse.
• La fonction MAX() est appliquée dans les instructions CASE pour agréger la réponse pour chaque combinaison de résultat et de question.
• Enfin, les résultats sont regroupés par ID de résultat, ID d'utilisateur et ID de travail pour créer la ligne souhaitée structure.

Cette requête donne le résultat suivant :

Version dynamique :

Si vous avez un nombre inconnu de questions (par exemple, 1 000), une version dynamique de la requête est plus adaptée. Ceci peut être réalisé en utilisant SQL dynamique et la concaténation de variables :

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;

Cette requête dynamique utilise la concaténation de variables pour créer dynamiquement l'instruction SQL en fonction du contenu de la table Question. Le résultat est une requête plus flexible qui peut traiter n'importe quel nombre de questions.

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!