J'ai des problèmes pour créer une table PIVOT dans MySQL. J'ai le tableau suivant. (c'est une table de démonstration réduite. La vraie a 4000 actions pour fe 10 tradedates et 20 valeurs de mesure.)
SET @sql = NULL; SELECT GROUP_CONCAT(concat(LScore2,' AS `LScore_',Stock_Short,'`')) into @sql from levermann; SET @sql = CONCAT('SELECT tradedate, ', @sql, ' FROM levermann GROUP BY tradedate'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
La sortie doit être une table où chaque STOCKCODE (par exemple ANDR.VI) de ces 8 doit être une colonne avec une valeur de mesure sélectionnable (par exemple LScore2) groupée par date d'échange (= ligne).
3 Réponses :
En SQL, vous ne pouvez pas écrire de requête qui développe les colonnes de manière dynamique en fonction des valeurs de données trouvées une fois qu'elles ont commencé à lire les données. Toutes les colonnes doivent être fixées dans la liste de sélection de la requête au moment où vous préparez la requête - avant qu'elle ne lise des données.
Cela signifie que vous devez connaître toutes les valeurs distinctes, et vous avez besoin pour produire une liste de sélection avec une colonne pour chaque valeur que vous souhaitez inclure dans le pivot.
Vous pouvez résoudre ce problème en effectuant deux requêtes: une pour récupérer toutes les valeurs de stock distinctes:
XXX
Ensuite, en fonction de ce résultat, formatez une longue requête SQL avec une expression pour chaque valeur de stock. Commencez par la colonne que vous voulez et qui n'est pas l'une des colonnes dynamiques liées aux actions:
FROM levermann GROUP BY Tradedate;
Ensuite, pour chaque ligne du résultat de la première requête, ajoutez une colonne comme:
MAX(CASE Stock_Short WHEN <value> THEN LScore2 END) AS <alias>,
Ensuite, ajoutez enfin la fin de la requête:
SELECT Tradedate,
Ma recommandation puisque vous avez 4000 valeurs d'actions distinctes est que vous devez simplement récupérer les données de la base de données telles quelles et utiliser le code d'application pour présenter un affichage pivoté. Autrement dit, faites une boucle sur le résultat de la requête SQL, les 4000 lignes (pas les colonnes) et organisez-les en un objet dans votre espace d'application. Formatez ensuite cet objet comme vous le souhaitez pour l'affichage.
Merci beaucoup, peut-être que vous avez raison, je pensais que je pourrais créer une déclaration dyn sql comme dans ma mise à jour ci-dessus.
Je pense que je l'ai:
SET SESSION group_concat_max_len = @@max_allowed_packet; SET @sql = NULL; SELECT GROUP_CONCAT( DISTINCT concat('MAX(CASE WHEN p.Stock_Short = \'',f.Stock_Short,'\' AND `Tradedate` = \'', f.tradedate,'\' THEN \'',f.LScore2,'\' ELSE NULL END) AS `LScore_',f.Stock_Short,'`')) into @sql from levermanndemo f ; SET @sql = CONCAT('SELECT p.tradedate, ', @sql, ' FROM levermanndemo p GROUP BY p.tradedate'); #SELECT @sql; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SORTIE:
Mise à jour: Malheureusement, il rend trouble si le tableau ressemble à ceci.
J'ai essayé cette solution:
SET SESSION group_concat_max_len = @@max_allowed_packet; SET @sql = NULL; SELECT GROUP_CONCAT(concat('MAX(CASE Stock_Short WHEN \'',Stock_Short,'\' THEN \'',LScore2,'\' END) AS `LScore_',Stock_Short,'`')) into @sql from levermanndemo where country = 'VI'; SET @sql = CONCAT('SELECT tradedate, ', @sql, ' FROM levermanndemo GROUP BY tradedate'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
La sortie n'est pas souhaitée - et fausse. Il y a maintenant 2 colonnes avec le même STOCK (BWO.VI et BWO.VI1), je voudrais fusionner ces deux colonnes ensemble. Mais comment faire?
"Je pense que je l'ai compris:" Très proche mais cela peut toujours échouer lorsque la valeur devient plus grande .. Je suggère également d'utiliser SET SESSION group_concat_max_len = @@ max_allowed_packet;
avant exécuter cette requête le manuel GROUP_CONCAT a> dit " Le résultat est tronqué à la longueur maximale qui est donnée par la variable système group_concat_max_len, qui a une valeur par défaut de 1024. " i > .. En général, lors de l'utilisation de GROUP_CONCAT
configurer le paramètre group_concat_max_len
@RaymondNijland MERCI BEAUCOUP, je réfléchissais vraiment et je n'ai pas trouvé la raison pour laquelle toutes les colonnes ne fonctionnent pas. De plus j'ai du mal à joindre les bonnes dates. les lignes sont pour toutes les dates sont les mêmes. Est-ce que tu sais pourquoi? Et comment pourrais-je créer une jointure externe gauche pour la corriger?
Le pivotage nécessite plus une syntaxe générale sous la forme off MAX (CASE Stock_Short WHEN
pas sûr de ce que vous faites ici ou de ce dont vous parlez.
@RaymondNijland Je ne comprends vraiment pas cette déclaration. Pourquoi le MAX? Je veux juste utiliser le LScore2 tel quel. Pouvez-vous l'expliquer un peu? Merci
@RaymondNijland je pourrais le faire, (voir la note mise à jour) merci beaucoup à vous deux!
Si la liste des StockCodes est fixe, ne peut-on pas simplement fixer les colonnes de la nouvelle table puis filtrer les données de la table d'origine pour les copier dans la nouvelle table?
Au moins, ce serait le processus lorsque vous le faites manuellement dans Excel par exemple.
merci, mais cela devrait être fait automatiquement. Je l'ai fait avec php avec succès maintenant.