Comment puis-je retourner la sortie du tableau croisé dynamique dans MySQL ?

Question

Si j'ai une table MySQL ressemblant à quelque chose comme ceci :

company\_name    action  pagecount
-------------------------------
Company A       PRINT   3
Company A       PRINT   2
Company A       PRINT   3
Company B       EMAIL   
Company B       PRINT   2
Company B       PRINT   2
Company B       PRINT   1
Company A       PRINT   3

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

company\_name    EMAIL   PRINT 1 pages   PRINT 2 pages   PRINT 3 pages
-------------------------------------------------------------
CompanyA        0       0               1               3
CompanyB        1       1               2               0

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

Answer 1

En gros, cela es un tableau croisé dynamique.

Vous trouverez ici un bon tutoriel sur la manière d'y parvenir : http://www.artfulsoftware.com/infotree/qrytip.php?id=78

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

Mise à jour

Après le lien ci-dessus n'est actuellement plus disponible, je me sens obligé de fournir quelques informations supplémentaires pour tous ceux qui cherchent des réponses mysql pivot ici. Il y avait vraiment une grande quantité d'informations, et je ne vais pas mettre tout ce qu'il y a là-dedans (même plus puisque je ne veux pas copier leur vaste connaissance), mais je vais donner quelques conseils sur la façon de traiter les tableaux croisés dynamiques à la manière 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 garderai un œil sur lui.

La méthode du tableur...

De nombreuses personnes utilisent simplement un outil comme MSExcel, OpenOffice ou d'autres tableurs à cette fin. C'est une solution valable, il suffit de copier les données là-bas et d'utiliser les outils que l'interface graphique offre pour résoudre ce problème.

Mais... ce n'était pas la question, et cela pourrait même entraîner certains inconvénients, comme la manière d'introduire les données dans le tableur, une mise à l'échelle problématique, etc.

La méthode SQL...

Étant donné que sa table ressemble à quelque chose comme ça :

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 rangées ( EMAIL , PRINT x pages ) ressemblent à des conditions. Le principal regroupement se fait par company_name .

Afin de mettre en place les conditions nécessaires, il convient d'utiliser l'outil d'évaluation de la qualité de l'eau. CASE -... déclaration. Pour regrouper par quelque chose, eh bien, utilisez ... GROUP BY .

Le SQL de base fournissant ce pivot peut ressembler à quelque chose comme 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 permettre d'obtenir 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é dynamique, plus vous devez définir de conditions dans votre instruction SQL.

Ce problème peut également être résolu, c'est pourquoi les gens ont tendance à utiliser des déclarations préparées, des routines, des compteurs, etc.

Quelques liens supplémentaires sur ce sujet :

• http://anothermysqldba.blogspot.de/2013/06/pivot-tables-example-in-mysql.html
• http://www.codeproject.com/Articles/363339/Cross-Tabulation-Pivot-Tables-with-MySQL
• http://datacharmer.org/downloads/pivot_tables_mysql_5.pdf
• https://codingsight.com/pivot-tables-in-mysql/

Answer 2

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

Answer 3

Pour MySQL, vous pouvez directement mettre des conditions dans SUM() et ce sera évalué comme booléen 0 o 1 et ainsi vous pouvez avoir votre compte basé sur vos critères sans utiliser IF/CASE déclarations

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

Answer 4

Pour un pivot dynamique, utilisez GROUP_CONCAT con CONCAT . Le site GROUPE_CONCAT concatène les chaînes d'un groupe en une seule chaîne avec diverses 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;

DEMO ICI

Answer 5

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 ;