Mesa dinámica de MySQL

Si tengo una tabla MySQL con algo como esto:

 nombre de la empresa action pagecount
 -------------------------------
 Compañía A PRINT 3
 Compañía A PRINT 2
 Compañía A PRINT 3
 Empresa B EMAIL   
 Empresa B IMPRESIÓN 2
 Empresa B IMPRESIÓN 2
 Compañía B IMPRESIÓN 1
 Compañía A PRINT 3

¿Es posible ejecutar una consulta MySQL para obtener resultados como este?

 company_name EMAIL PRINT 1 página IMPRIMIR 2 páginas IMPRIMIR 3 páginas
 -------------------------------------------------- -----------
 CompanyA 0 0 1 3
 EmpresaB 1 1 2 0

La idea es que el pagecount puede variar, por lo que el monto de la columna de salida debe reflejar eso, una columna por cada par de action / pagecount y luego el número de visitas por nombre de compañía. No estoy seguro de si esto se llama una tabla dinámica, pero alguien sugirió eso?

Esto básicamente es una tabla dinámica.

Un buen tutorial sobre cómo lograr esto se puede encontrar aquí: http://www.artfulsoftware.com/infotree/qrytip.php?id=78

Aconsejo leer esta publicación y adaptar esta solución a sus necesidades.

Actualizar

Después de que el enlace de arriba ya no esté disponible, me siento obligado a proporcionar información adicional para todos ustedes que buscan respuestas dinámicas de mysql aquí. Realmente tenía una gran cantidad de información, y no voy a poner todo aquí (aún más, ya que simplemente no quiero copiar sus amplios conocimientos), pero voy a dar algunos consejos sobre cómo tratar con pivote tablas la manera sql generalmente con el ejemplo de peku que hizo la pregunta en primer lugar.

Tal vez el enlace vuelva pronto, lo mantendré atento.

La forma de hoja de cálculo …

Muchas personas simplemente usan una herramienta como MSExcel, OpenOffice u otras herramientas de hoja de cálculo para este propósito. Esta es una solución válida, solo copie los datos allí y use las herramientas que la GUI ofrece para resolver esto.

Pero … esta no era la pregunta, e incluso podría llevar a algunas desventajas, por ejemplo, cómo obtener los datos en la hoja de cálculo, escalamiento problemático, etc.

La forma de SQL …

Dado que su mesa se ve más o menos así:

 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; 

Ahora mira en su tabla deseada:

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

Las filas ( EMAIL , PRINT x pages ) se parecen a las condiciones. La agrupación principal es por company_name .

Para configurar las condiciones, esto más bien grita por usar la statement CASE . Para agrupar por algo, bueno, usa … GROUP BY .

El SQL básico que proporciona este pivote puede verse más o menos así:

 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`; 

Esto debería proporcionar el resultado deseado muy rápido. La principal desventaja de este enfoque, cuantas más filas desee en su tabla dinámica, más condiciones deberá definir en su statement de SQL.

Esto también se puede tratar, por lo tanto, las personas tienden a usar declaraciones preparadas, rutinas, contadores y demás.

Algunos enlaces adicionales sobre este tema:

Mi solución está en T-SQL sin ningún pivote:

 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 

Para MySQL puede poner condiciones directamente en la función SUM() y se evaluará como Boolean 0 o 1 y así puede tener su recuento según sus criterios sin usar sentencias 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

Para el pivote dynamic, use GROUP_CONCAT con CONCAT . La función GROUP_CONCAT concatena cadenas de un grupo en una cadena con varias opciones.

 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 AQUÍ

Una versión Stardard-SQL con lógica booleana :

 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; 

SQL Fiddle.

¿Cómo?

TRUE OR NULL produce TRUE .
FALSE OR NULL produce NULL .
NULL OR NULL produce NULL .
Y COUNT solo cuenta valores no nulos. Voilá.

Existe una herramienta llamada generador de tablas pivote MySQL, que puede ayudarlo a crear una tabla dinámica basada en la web que luego puede exportar a Excel (si lo desea). puede funcionar si sus datos están en una sola tabla o en varias tablas.

Todo lo que necesita hacer es especificar la fuente de datos de las columnas (admite columnas dinámicas), las filas, los valores en el cuerpo de la tabla y la relación de la tabla (si hay alguna) MySQL Pivot Table

La página de inicio de esta herramienta es http://mysqlpivottable.net

La respuesta correcta es:

 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 ;