instalar - mysql for excel

Tabla pivote de MySQL

Question

Si tengo una tabla MySQL parecida a esto:

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

¿Es posible ejecutar una consulta de MySQL para obtener una salida como esta:

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

La idea es que pagecount puede variar, por lo que la cantidad de la columna de salida debe reflejar eso, una columna para 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?

Answer 1

Esta es básicamente 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 este post y adaptar esta solución a sus necesidades.

Actualizar

Después de que el enlace anterior ya no esté disponible, me siento obligado a proporcionar información adicional para todos ustedes que buscan las respuestas de mysql pivot aquí. Realmente tenía una gran cantidad de información, y no pondré todo desde aquí (aún más porque no quiero copiar su vasto conocimiento), pero daré algunos consejos sobre cómo lidiar con el pivote. tablas de la forma sql generalmente con el ejemplo de peku que hizo la pregunta en primer lugar.

Tal vez el enlace vuelva pronto, estaré 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 conllevar algunas desventajas, como la forma de incluir los datos en la hoja de cálculo, el escalamiento problemático, etc.

La forma SQL ...

Dada su mesa se ve algo 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 mesa 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 asemejan a las condiciones. La agrupación principal es por nombre de compañía.

Con el fin de configurar las condiciones, más bien grita para usar la declaración CASE . Para agrupar por algo, bueno, usa ... GROUP BY .

El SQL básico que proporciona este pivote puede verse algo 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. El mayor inconveniente de este enfoque, cuantas más filas desee en su tabla dinámica, más condiciones necesita definir en su declaración SQL.

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

Algunos enlaces adicionales sobre este tema:

• 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
• http://buysql.com/mysql/14-how-to-automate-pivot-tables.html

Answer 2

Existe una herramienta llamada MySQL Pivot table generator, que puede ayudarlo a crear una tabla dinámica basada en 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 el origen 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)

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

Answer 3

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

Answer 4

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

Answer 5

Para MySQL puede poner condiciones directamente en la función SUM() y se evaluará como Boolean 0 o 1 y, por lo tanto, puede tener su conteo basado en sus criterios sin usar declaraciones 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

Answer 6

Para un pivote dinámico, 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Í

Answer 7

Una versión estándar de SQL usando 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;

El violín de SQL.

¿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á.

Answer 8

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 ;