Tableau croisé MySQL

Si j’ai une table MySQL qui ressemble à ceci:

 nom_entreprise nom_action action
 -------------------------------
 Société A IMPRIMER 3
 Société A IMPRIMER 2
 Société A IMPRIMER 3
 Société B EMAIL   
 Société B IMPRIMER 2
 Société B IMPRIMER 2
 Société B IMPRIMER 1
 Société A IMPRIMER 3

Est-il possible d’exécuter une requête MySQL pour obtenir un résultat comme celui-ci:

 company_name IMPRIMER EMAIL 1 pages IMPRIMER 2 pages IMPRIMER 3 pages
 -------------------------------------------------- -----------
 CompanyA 0 0 1 3
 CompanyB 1 1 2 0

L’idée est que pagecount peut varier, de sorte que le montant de la colonne de sortie doit refléter cela, une colonne pour chaque paire action / pagecount et ensuite le nombre de visites par company_name . Je ne suis pas sûr si cela s’appelle un tableau croisé dynamic, mais quelqu’un l’a suggéré?

    Ceci est essentiellement un tableau croisé dynamic.

    Un bon tutoriel sur la façon d’y parvenir peut être trouvé ici: http://www.artfulsoftware.com/infotree/qrytip.php?id=78

    Je vous conseille de lire ce post et d’adapter cette solution à vos besoins.

    Mettre à jour

    Une fois que le lien ci-dessus n’est plus disponible, je me sens obligé de fournir des informations supplémentaires à tous ceux qui recherchent des réponses mysql pivot ici. Il y avait vraiment une grande quantité d’informations, et je ne mettrais pas tout de là (même si je ne veux pas copier leurs vastes connaissances), mais je donnerai quelques conseils sur la manière de gérer pivot. tables la manière de SQL généralement avec l’exemple de peku qui a posé la question en premier lieu.

    Peut-être que le lien reviendra bientôt, je vais garder un œil dessus.

    Le tableur

    De nombreuses personnes utilisent simplement un outil comme MSExcel, OpenOffice ou d’autres outils de tableur à cette fin. Ceci est une solution valide, copiez simplement les données là-bas et utilisez les outils proposés par l’interface graphique pour résoudre ce problème.

    Mais … ce n’était pas la question, et cela pourrait même entraîner certains inconvénients, tels que la manière d’obtenir les données dans la feuille de calcul, la mise à l’échelle problématique, etc.

    La manière SQL …

    Étant donné que sa table ressemble à ceci:

     CREATE TABLE `test_pivot` ( `pid` bigint(20) NOT NULL AUTO_INCREMENT, `company_name` varchar(32) DEFAULT NULL, `action` varchar(16) DEFAULT NULL, `pagecount` bigint(20) DEFAULT NULL, PRIMARY KEY (`pid`) ) ENGINE=MyISAM; 

    Maintenant, regardez dans sa table désirée:

     company_name EMAIL PRINT 1 pages PRINT 2 pages PRINT 3 pages ------------------------------------------------------------- CompanyA 0 0 1 3 CompanyB 1 1 2 0 

    Les lignes ( EMAIL , PRINT x pages ) ressemblent à des conditions. Le regroupement principal est par company_name .

    Afin de définir les conditions, il est préférable d’utiliser l’état CASE . Pour regrouper par quelque chose, eh bien, utilisez … GROUP BY .

    Le SQL de base fournissant ce pivot peut ressembler à ceci:

     SELECT P.`company_name`, COUNT( CASE WHEN P.`action`='EMAIL' THEN 1 ELSE NULL END ) AS 'EMAIL', COUNT( CASE WHEN P.`action`='PRINT' AND P.`pagecount` = '1' THEN P.`pagecount` ELSE NULL END ) AS 'PRINT 1 pages', COUNT( CASE WHEN P.`action`='PRINT' AND P.`pagecount` = '2' THEN P.`pagecount` ELSE NULL END ) AS 'PRINT 2 pages', COUNT( CASE WHEN P.`action`='PRINT' AND P.`pagecount` = '3' THEN P.`pagecount` ELSE NULL END ) AS 'PRINT 3 pages' FROM test_pivot P GROUP BY P.`company_name`; 

    Cela devrait fournir le résultat souhaité très rapidement. L’inconvénient majeur de cette approche est que plus vous voulez de lignes dans votre tableau croisé dynamic, plus vous devez définir de conditions dans votre instruction SQL.

    Cela peut aussi être traité, donc les gens ont tendance à utiliser des instructions, des routines, des compteurs et autres.

    Quelques liens supplémentaires sur ce sujet:

    Ma solution est en T-SQL sans aucun pivot:

     SELECT CompanyName, SUM(CASE WHEN (action='EMAIL') THEN 1 ELSE 0 END) AS Email, SUM(CASE WHEN (action='PRINT' AND pagecount=1) THEN 1 ELSE 0 END) AS Print1Pages, SUM(CASE WHEN (action='PRINT' AND pagecount=2) THEN 1 ELSE 0 END) AS Print2Pages, SUM(CASE WHEN (action='PRINT' AND pagecount=3) THEN 1 ELSE 0 END) AS Print3Pages FROM Company GROUP BY CompanyName 

    Pour MySQL, vous pouvez directement mettre des conditions dans la fonction SUM() et il sera évalué en tant que booléen 0 ou 1 et vous pourrez donc avoir votre compte en fonction de vos critères sans utiliser les instructions IF/CASE

     SELECT company_name, SUM(action = 'EMAIL')AS Email, SUM(action = 'PRINT' AND pagecount = 1)AS Print1Pages, SUM(action = 'PRINT' AND pagecount = 2)AS Print2Pages, SUM(action = 'PRINT' AND pagecount = 3)AS Print3Pages FROM t GROUP BY company_name 

    DEMO

    Pour le pivot dynamic, utilisez GROUP_CONCAT avec CONCAT . La fonction GROUP_CONCAT concatène des chaînes d’un groupe en une chaîne avec différentes options.

     SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'SUM(CASE WHEN action = "', action,'" AND ', (CASE WHEN pagecount IS NOT NULL THEN CONCAT("pagecount = ",pagecount) ELSE pagecount IS NULL END), ' THEN 1 ELSE 0 end) AS ', action, IFNULL(pagecount,'') ) ) INTO @sql FROM t; SET @sql = CONCAT('SELECT company_name, ', @sql, ' FROM t GROUP BY company_name'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; 

    DÉMO ICI

    Une version stardard-SQL utilisant la logique booléenne :

     SELECT company_name , COUNT(action = 'EMAIL' OR NULL) AS "Email" , COUNT(action = 'PRINT' AND pagecount = 1 OR NULL) AS "Print 1 pages" , COUNT(action = 'PRINT' AND pagecount = 2 OR NULL) AS "Print 2 pages" , COUNT(action = 'PRINT' AND pagecount = 3 OR NULL) AS "Print 3 pages" FROM tbl GROUP BY company_name; 

    Violon SQL

    Comment?

    TRUE OR NULL donne TRUE .
    FALSE OR NULL donne NULL .
    NULL OR NULL donne NULL .
    Et COUNT ne compte que les valeurs non nulles. Voilá.

    Il existe un outil appelé MySQL Pivot Table Generator, qui peut vous aider à créer un tableau croisé dynamic basé sur le Web que vous pourrez ensuite exporter vers Excel (si vous le souhaitez). Cela peut fonctionner si vos données se trouvent dans une seule table ou dans plusieurs tables.

    Tout ce que vous avez à faire est de spécifier la source de données des colonnes (il prend en charge les colonnes dynamics), les lignes, les valeurs dans le corps de la table et la relation entre les tables (le cas échéant). Tableau croisé MySQL

    La page d’accueil de cet outil est http://mysqlpivottable.net

    La réponse correcte est:

     select table_record_id, group_concat(if(value_name='note', value_text, NULL)) as note ,group_concat(if(value_name='hire_date', value_text, NULL)) as hire_date ,group_concat(if(value_name='termination_date', value_text, NULL)) as termination_date ,group_concat(if(value_name='department', value_text, NULL)) as department ,group_concat(if(value_name='reporting_to', value_text, NULL)) as reporting_to ,group_concat(if(value_name='shift_start_time', value_text, NULL)) as shift_start_time ,group_concat(if(value_name='shift_end_time', value_text, NULL)) as shift_end_time from other_value where table_name = 'employee' and is_active = 'y' and is_deleted = 'n' GROUP BY table_record_id 
     select t3.name, sum(t3.prod_A) as Prod_A, sum(t3.prod_B) as Prod_B, sum(t3.prod_C) as Prod_C, sum(t3.prod_D) as Prod_D, sum(t3.prod_E) as Prod_E from (select t2.name as name, case when t2.prodid = 1 then t2.counts else 0 end prod_A, case when t2.prodid = 2 then t2.counts else 0 end prod_B, case when t2.prodid = 3 then t2.counts else 0 end prod_C, case when t2.prodid = 4 then t2.counts else 0 end prod_D, case when t2.prodid = "5" then t2.counts else 0 end prod_E from (SELECT partners.name as name, sales.products_id as prodid, count(products.name) as counts FROM test.sales left outer join test.partners on sales.partners_id = partners.id left outer join test.products on sales.products_id = products.id where sales.partners_id = partners.id and sales.products_id = products.id group by partners.name, prodid) t2) t3 group by t3.name ;