Consulta à tabela dinâmica do MySQL com colunas dinâmicas

Eu estou usando as seguintes tabelas para armazenar dados do produto:

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 a seguinte consulta para selecionar os registros de ambas as tabelas

 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 | +---------------+---------------+--------+---------+--------+ 

E tudo está funcionando corretamente 🙂

Como eu preenchei a tabela ‘adicional’ dinamicamente, seria bom se a consulta também fosse dinâmica. Dessa forma eu não tenho que mudar a consulta toda vez que eu colocar em um novo fieldname e fieldvalue.

A única maneira no MySQL de fazer isso dinamicamente é com instruções preparadas. Aqui está um bom artigo sobre eles:

Tabelas dinâmicas dinâmicas (transformar linhas em colunas)

Seu código ficaria assim:

 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; 

Veja SQL Fiddle com Demo

NOTA: A function GROUP_CONCAT tem um limite de 1024 caracteres. Veja o parâmetro group_concat_max_len

Eu tenho uma maneira ligeiramente diferente de fazer isso do que a resposta aceita. Desta forma, você pode evitar o uso de GROUP_CONCAT, que tem um limite de 1024 caracteres e não funcionará se você tiver muitos 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; 

Aqui está o procedimento armazenado, que irá gerar a tabela com base nos dados de uma tabela e coluna e dados de outra tabela e coluna.

A function ‘sum (if (col = valor, 1,0)) como valor’ é usada. Você pode escolher entre diferentes funções 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 ;