Comment transposer une table SQL avec des colonnes générées dynamiquement ?

Transposer une table SQL : réaliser une transformation flexible des données

Les opérations de transposition en SQL impliquent la conversion de lignes en colonnes et vice versa. Dans ce contexte, abordons le problème de la transposition d'une table avec des noms de champs définis par l'utilisateur pour des données spécifiques à l'utilisateur.

Problème :

Étant donné une table avec l'utilisateur- noms de champs définis et valeurs correspondantes, le but est de le transposer dans un format où les données de chaque utilisateur sont présentées sur une seule ligne, avec des noms de colonnes représentant le champ noms.

Exemple :

Tableau d'entrée :

Id	UserId	FieldName	FieldValue
1	100	Username	John Doe
2	100	Password	pass123!
3	102	Username	Jane
4	102	Password	$ecret
5	102	Email Address	[email protected]

Sortie souhaitée :

UserId	Username	Password	Email Address
100	John Doe	pass123!	NULL
102	Jane	$ecret	[email protected]

Solution dans MySQL (sans syntaxe ANSI) :

Comme MySQL ne le fait pas prenant en charge la syntaxe ANSI PIVOT/UNPIVOT, nous pouvons utiliser une approche d'agrégation conditionnelle :

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 TABLE t
GROUP BY t.userid

Cette approche parcourt chaque ligne et utilise les instructions CASE pour attribuer des valeurs aux colonnes de sortie souhaitées en fonction du nom du champ. Il agrège ensuite les résultats pour chaque ID utilisateur.

Solution dynamique utilisant des instructions préparées :

Pour rendre la solution plus polyvalente, nous pouvons utiliser la syntaxe d'instruction préparée de MySQL, qui permet nous de spécifier dynamiquement les champs :

SET @query = "SELECT userid, ";
SET @enum_query = "";
SELECT CONCAT(@enum_query, "MAX(CASE WHEN fieldname = '", fieldname, "' THEN fieldvalue ELSE NULL END) AS ", fieldname,", ")
INTO @enum_query
FROM (
    SELECT DISTINCT fieldname
    FROM TABLE
) AS subquery;
SET @enum_query = SUBSTRING(@enum_query, 1, LENGTH(@enum_query) - 2);
SET @query = CONCAT(@query, @enum_query, " FROM TABLE GROUP BY userid;");
PREPARE stmt FROM @query;
EXECUTE stmt;

Cette approche crée une requête dynamique qui inclut tous les noms de champs souhaités sous forme de colonnes dans le sortie.

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!