45

I'm using the following tables for storing product data:

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

Using the following query to select the records from both tables

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

And everything is working correctly :)

Because i fill the 'additional' table dynamically it would be nice, if the query would also be dynamic. In that way i dont have to change the query everytime i put in a new fieldname and fieldvalue.

Improve this question
3

4 Answers 4

Reset to default
47

The only way in MySQL to do this dynamically is with Prepared statements. Here is a good article about them:

Dynamic pivot tables (transform rows to columns)

Your code would look like this:

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;

See Demo

NOTE: GROUP_CONCAT function has a limit of 1024 characters. See parameter group_concat_max_len

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Thanks for your quick answer! So in PHP this would mean, that i do 2 queries. One for getting the string of columns and the second one where i concat the string into the full query? Using Zend Framework here.
@fr0sty you will have to test it in PHP. I am not sure exactly how you would proceed in that code.
I just used this example and reapplied it to pull data from wp_posts and wp_postmeta and it was a huge help. Thank you. The only thing I needed to add was the call to SET group_concat_max_len=150000; so it would not give warning.
This was a great help. I applied this method to similiar problem and rather than concat a SELECT statement I created an event scheduled every couple hours to rebuild a view that pivots n amount of rows from one table into n columns on the other. It's a big help because before I was rebuilding the query using PHP on every execution of the SELECT. Even though views can't leverage Indexes, I'm thinking filtering performance won't be an issue as the pivoted rows->columns represent types of training employees at a franchise have so the view won't ever break a few thousand rows. THANKS @bluefeet!
@JuanCarlosOropeza Thanks for letting me know, I replaced the link with a db<>fiddle.
|
11

I have a slightly different way of doing this than the accepted answer. This way you can avoid using GROUP_CONCAT which has a limit of 1024 characters by default and will not work if you have a lot of fields unless you change the limit.

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;

Click here for demo

Improve this answer

7 Comments

I'm receiving an error in running this in mySQL workbench. How to get this run?
well it works for me. It would depend on what error you are getting
By the way the limit of group_concat can be defined, SET SESSION group_concat_max_len = 1000000; Above is set for SESSION SET GLOBAL group_concat_max_len = 1000000;
@GrumpyCivet The issue you are having is most likely due to having ONLY_FULL_GROUP_BY enabled. Simple solution is to fill in the rest of the columns in the Group By clause of the generated query.... Which I have now done. So it should work. Here is a fiddle of it working db-fiddle.com/f/vysYCm6TKznUPBopV9WpjF/0
Clever solution! I've modified it slightly for my uses. If you don't want more than one result returned, limit the first select statement to the last row, and dump the result into a variable. Example here: db-fiddle.com/f/cJ2RXHBDQ4iybur59DsHB2/1
|
2

Here's stored procedure, which will generate the table based on data from one table and column and data from other table and column.

The function 'sum(if(col = value, 1,0)) as value ' is used. You can choose from different functions like 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 ;
Improve this answer

Comments

0

This piece of code works for me

SET @sql_1 = NULL;
SET @sql_2 = NULL; 

SELECT
GROUP_CONCAT(DISTINCT CONCAT(
'max( CASE WHEN descripcion_material = "', descripcion_material, '" THEN peso_teorico ELSE 0 END) AS `', CONCAT(descripcion_material,'_T') , '`'
))
INTO @sql_1
FROM despacho_carguios;

SELECT
GROUP_CONCAT(DISTINCT CONCAT(
'max( CASE WHEN descripcion_material = "', descripcion_material, '" THEN peso_real ELSE 0 END) AS `', CONCAT(descripcion_material,'_R') , '`')
)
INTO @sql_2
FROM despacho_carguios;

SET @sql_1 = CONCAT('SELECT 
 
 `id_programacion`
, `fecha`
, `diseno`
, `hora`
, `volumen`
, `operador`
, `mixer`
, `por_cargar`
, `created_at`,

', @sql_1,',', @sql_2 ,' FROM despacho_carguios 
GROUP BY 
 `id_programacion`
, `fecha`
, `diseno`
, `hora`
, `volumen`
, `operador`
, `mixer`
, `por_cargar`
, `created_at`


');


SELECT @sql_1;
PREPARE stmt FROM @sql_1;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Improve this answer

1 Comment

How is this different from the other answers?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.