MySQL pivot table query with dynamic columns

by

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

More Related Contents:

  1. How can I return pivot table output in MySQL?
  2. MySQL pivot row into dynamic number of columns
  3. How To have Dynamic SQL in MySQL Stored Procedure
  4. What is the opposite of GROUP_CONCAT in MySQL?
  5. How to pivot a MySQL entity-attribute-value schema
  6. Mysql query to dynamically convert rows to columns
  7. mysql pivot/crosstab query
  8. MySQL dynamic-pivot
  9. Mysql Convert Column to row (Pivot table )
  10. Mysql query to dynamically convert rows to columns on the basis of two columns
  11. How to transpose mysql table rows into columns
  12. Setting column values as column names in the SQL query result
  13. mysql pivot query results with GROUP BY
  14. MySQL Pivot Table Column Data as Rows
  15. Transpose rows into columns in MySQL
  16. Join two tables (with a 1-M relationship) where the second table needs to be ‘flattened’ into one row
  17. Using LIMIT within GROUP BY to get N results per group?
  18. Delete with Join in MySQL
  19. Suppress warning messages using mysql from within Terminal, but password written in bash script
  20. MYSQL ORDER BY CASE Issue
  21. Can’t use a MySQL connection for entity framework 6
  22. MySQL difference between two rows of a SELECT Statement
  23. MySQL view performance
  24. How to retrieve JSON data from MySQL?
  25. Is incrementing a field in MySQL atomic?
  26. Delete from one table with join
  27. MySQL column type “TIMESTAMP” implicitly includes “NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP”
  28. MySQL: Compare BETWEEN time
  29. MySQL: how to do row-level security (like Oracle’s Virtual Private Database)?
  30. Count duplicates records in Mysql table?

Leave a Comment