MySQL pivot table query with dynamic columns

Question

I m using the following tables for storing product data   mysql gt  SELECT   FROM product   --------------- --------------- --------    id   name       description     stock     --------------- --------------- --------     1   product1   first product      5         2   product2   second product     5       --------------- --------------- --------   mysql gt  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 gt  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

User · Accepted Answer

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');

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

User · Answer

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 and will not work if you have a lot of fields   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

User · Answer

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

[mysql] MySQL pivot table query with dynamic columns

Examples related to mysql

Examples related to dynamic

Examples related to pivot