Consulta de tablas dinámicas MySQL con columnas dinámicas

Estoy usando las siguientes tablas para almacenar datos de productos:

mysql> SELECT * FROM product; +---------------+---------------+--------+ | id | name | description | stock | +---------------+---------------+--------+ | 1 | product1 | first product | 5 | | 2 | product2 | second product| 5 | +---------------+---------------+--------+ mysql> SELECT * FROM product_additional; +-----------------+------------+ | id | fieldname | fieldvalue | +-----------------+------------+ | 1 | size | S | | 1 | height | 103 | | 2 | size | L | | 2 | height | 13 | | 2 | color | black | +-----------------+------------+ 

Usando la siguiente consulta para seleccionar los registros de ambas tablas

 mysql> SELECT p.id , p.name , p.description ,MAX(IF(pa.fieldname = 'size', pa.fieldvalue, NULL)) as `size` ,MAX(IF(pa.fieldname = 'height', pa.fieldvalue, NULL)) as `height` ,MAX(IF(pa.fieldname = 'color', pa.fieldvalue, NULL)) as `color` FROM product p LEFT JOIN product_additional AS pa ON p.id = pa.id GROUP BY p.id +---------------+---------------+--------+---------+--------+ | id | name | description | size | height | color | +---------------+---------------+--------+---------+--------+ | 1 | product1 | first product | S | 103 | null | | 2 | product2 | second product| L | 13 | black | +---------------+---------------+--------+---------+--------+ 

Y todo está funcionando correctamente 🙂

Como llene dinámicamente la tabla “adicional”, sería bueno que la consulta también fuera dinámica. De esa forma, no tengo que cambiar la consulta cada vez que pongo un nuevo nombre de campo y valor de campo.

La única manera en MySQL para hacer esto dinámicamente es con las declaraciones preparadas. Aquí hay un buen artículo sobre ellos:

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

Tu código se vería así:

 SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(IF(pa.fieldname = ''', fieldname, ''', pa.fieldvalue, NULL)) AS ', fieldname ) ) INTO @sql FROM product_additional; SET @sql = CONCAT('SELECT p.id , p.name , p.description, ', @sql, ' FROM product p LEFT JOIN product_additional AS pa ON p.id = pa.id GROUP BY p.id'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; 

Ver SQL Fiddle con demostración

NOTA: la función GROUP_CONCAT tiene un límite de 1024 caracteres. Ver parámetro group_concat_max_len

Tengo una forma ligeramente diferente de hacer esto que la respuesta aceptada. De esta forma puede evitar el uso de GROUP_CONCAT que tiene un límite de 1024 caracteres y no funcionará si tiene muchos campos.

 SET @sql = ''; SELECT @sql := CONCAT(@sql,if(@sql='','',', '),temp.output) FROM ( SELECT DISTINCT CONCAT( 'MAX(IF(pa.fieldname = ''', fieldname, ''', pa.fieldvalue, NULL)) AS ', fieldname ) as output FROM product_additional ) as temp; SET @sql = CONCAT('SELECT p.id , p.name , p.description, ', @sql, ' FROM product p LEFT JOIN product_additional AS pa ON p.id = pa.id GROUP BY p.id'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; 

Aquí está el procedimiento almacenado, que generará la tabla basada en datos de una tabla y columna y datos de otra tabla y columna.

Se usa la función ‘sum (si (col = valor, 1,0)) como valor’. Puede elegir entre diferentes funciones como MAX (if ()) etc.

 delimiter // create procedure myPivot( in tableA varchar(255), in columnA varchar(255), in tableB varchar(255), in columnB varchar(255) ) begin set @sql = NULL; set @sql = CONCAT('select group_concat(distinct concat( \'SUM(IF(', columnA, ' = \'\'\',', columnA, ',\'\'\', 1, 0)) AS \'\'\',', columnA, ',\'\'\'\') separator \', \') from ', tableA, ' into @sql'); -- select @sql; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- select @sql; SET @sql = CONCAT('SELECT p.', columnB, ', ', @sql, ' FROM ', tableB, ' p GROUP BY p.', columnB,''); -- select @sql; /* */ PREPARE stmt FROM @sql; EXECUTE stmt; /* */ DEALLOCATE PREPARE stmt; end// delimiter ;