Comment transposer dynamiquement une table en SQL à l'aide d'instructions préparées ?

Comment transposer une table à l'aide de Dynamic SQL

En SQL, transposer une table implique de convertir une table orientée lignes en une table orientée colonnes. Cela peut être utile pour divers scénarios, tels que la création d'un rapport de synthèse utilisateur.

Une approche pour réaliser la transposition en SQL consiste à utiliser l'instruction CASE. Considérons une table avec des colonnes : Id, UserId, FieldName et FieldValue, comme indiqué dans l'exemple fourni.

SELECT t.userid,
       MAX(CASE WHEN t.fieldname = 'Username' THEN t.fieldvalue ELSE NULL END) AS Username,
       MAX(CASE WHEN t.fieldname = 'Password' THEN t.fieldvalue ELSE NULL END) AS Password,
       MAX(CASE WHEN t.fieldname = 'Email Address' THEN t.fieldvalue ELSE NULL END) AS Email
FROM t
GROUP BY t.userid

Cette requête renverra le résultat transposé, regroupant les utilisateurs par UserId et attribuant les valeurs de champ correspondantes à les colonnes Nom d'utilisateur, Mot de passe et Adresse e-mail.

Cependant, cette approche nécessite de définir des instructions CASE pour chaque champ, ce qui peut être fastidieux et inflexible. Pour rendre cela dynamique, vous pouvez utiliser la syntaxe d'instruction préparée (SQL dynamique) de MySQL.

SET @sql = CONCAT("SELECT userid");
-- Dynamically create the field selection part
SET @field_list = (
  SELECT GROUP_CONCAT(
    CONCAT('MAX(CASE WHEN t.fieldname = ''', fieldname, ''' THEN t.fieldvalue ELSE NULL END) AS ', fieldname)
  )
  FROM t
  GROUP BY userid
);
SET @sql = CONCAT(@sql, ", ", @field_list);

SET @sql = CONCAT(@sql, " FROM t GROUP BY userid");
PREPARE stmt FROM @sql;
EXECUTE stmt;

Cette requête construit une instruction SQL dynamique en utilisant le résultat d'une sous-requête pour déterminer la partie de sélection de champ. Il utilise la fonction GROUP_CONCAT pour concaténer les instructions CASE, garantissant ainsi la validité de la requête SQL construite dynamiquement.

En utilisant le SQL dynamique, vous pouvez éviter de définir manuellement les instructions CASE et conserver la flexibilité, permettant à votre requête de gérer n'importe quel nombre. de champs définis par l'utilisateur.

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!