Consulta de tabla dinámica 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 funciona correctamente :)
Debido a que completo la tabla 'adicional' dinámicamente, sería bueno si la consulta también fuera dinámica. De esa manera no tengo que cambiar la consulta cada vez que ingreso un nuevo nombre de campo y valor de campo.
La única forma en MySQL de hacer esto dinámicamente es con declaraciones preparadas. Aquí hay un buen artículo sobre ellos:
Tablas dinámicas dinámicas (transforma filas en columnas)
Su 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, p.name, p.description');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Ver 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 manera puedes evitar usar GROUP_CONCAT que tiene un límite de 1024 caracteres por defecto y no funcionará si tienes muchos campos a menos que cambies el límite.
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, p.name, p.description');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Haga clic aquí para una demostración
Aquí está el procedimiento almacenado, que generará la tabla en función de los datos de una tabla y columna y los datos de otra tabla y columna.
Se utiliza la función 'suma (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 ;
