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

Resuelto FeeJai asked hace 12 años • 1 respuestas

Digamos que tengo tres tablas MySQL diferentes:

Mesa products:

id | name
 1   Product A
 2   Product B

Mesa partners:

id | name
 1   Partner A
 2   Partner B

Mesa sales:

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 en su lugar algo 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 de 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.

FeeJai avatar Aug 17 '12 18:08 FeeJai
Aceptado

Desafortunadamente, MySQL no tiene una PIVOTfunción que es básicamente lo que estás intentando hacer. Por lo tanto, necesitarás usar una función agregada con una CASEdeclaración:

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 demostración de SQL

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

Con tablas dinámicas dinámicas (transformar filas en columnas), su 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 demostración de SQL

Probablemente valga la pena señalar que GROUP_CONCATestá limitado de forma predeterminada a 1024 bytes. Puede solucionar esto configurándolo en un valor más alto durante la duración del procedimiento, es decir.SET @@group_concat_max_len = 32000;

Taryn avatar Aug 17 '2012 12:08 Taryn