tablas filas dinamicas convertir columnas mysql sql pivot

filas - Fila dinámica de MySQL en número dinámico de columnas



convertir filas en columnas mysql (1)

Digamos que tengo tres tablas MySQL diferentes:

products mesa:

id | name 1 Product A 2 Product B

partners mesa:

id | name 1 Partner A 2 Partner B

sales mesa:

partners_id | products_id 1 2 2 5 1 5 1 3 1 4 1 5 2 2 2 4 2 3 1 1

Me gustaría obtener una tabla con socios en las filas y productos como columnas. Hasta ahora pude obtener un resultado como este:

name | name | COUNT( * ) Partner A Product A 1 Partner A Product B 1 Partner A Product C 1 Partner A Product D 1 Partner A Product E 2 Partner B Product B 1 Partner B Product C 1 Partner B Product D 1 Partner B Product E 1

Usando esta consulta:

SELECT partners.name, products.name, COUNT( * ) FROM sales JOIN products ON sales.products_id = products.id JOIN partners ON sales.partners_id = partners.id GROUP BY sales.partners_id, sales.products_id LIMIT 0 , 30

pero me gustaría tener algo así como:

partner_name | Product A | Product B | Product C | Product D | Product E Partner A 1 1 1 1 2 Partner B 0 1 1 1 1

El problema es que no puedo decir cuántos productos tendré, por lo que el número de columna debe cambiar dinámicamente según las filas en la tabla de productos.

Esta muy buena respuesta no parece funcionar con mysql: T-SQL Pivot? Posibilidad de crear columnas de tabla a partir de valores de fila


Desafortunadamente, MySQL no tiene una función PIVOT , que es básicamente lo que estás tratando de hacer. Por lo tanto, deberá usar una función agregada con una instrucción CASE :

select pt.partner_name, count(case when pd.product_name = ''Product A'' THEN 1 END) ProductA, count(case when pd.product_name = ''Product B'' THEN 1 END) ProductB, count(case when pd.product_name = ''Product C'' THEN 1 END) ProductC, count(case when pd.product_name = ''Product D'' THEN 1 END) ProductD, count(case when pd.product_name = ''Product E'' THEN 1 END) ProductE from partners pt left join sales s on pt.part_id = s.partner_id left join products pd on s.product_id = pd.prod_id group by pt.partner_name

Ver SQL Fiddle con demostración

Como no conoce los Productos, probablemente quiera realizar esto de forma dinámica. Esto se puede hacer usando declaraciones preparadas.

Tablas dinámicas dinámicas (transformar filas en columnas)

Tu código se vería así:

SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( ''count(case when Product_Name = '''''', Product_Name, '''''' then 1 end) AS '', replace(Product_Name, '' '', '''') ) ) INTO @sql from products; SET @sql = CONCAT(''SELECT pt.partner_name, '', @sql, '' from partners pt left join sales s on pt.part_id = s.partner_id left join products pd on s.product_id = pd.prod_id group by pt.partner_name''); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

Ver SQL Fiddle con demostración

Probablemente valga la pena señalar que GROUP_CONCAT está limitado de forma predeterminada a 1024 bytes. Puede evitar esto al establecerlo más alto durante la duración de su procedimiento, es decir. SET @@group_concat_max_len = 32000;