MySQL dynamic-pivot

by

Unfortunately, MySQL does not have a PIVOT function but you can model it using an aggregate function and a CASE statement. For a dynamic version, you will need to use prepared statements:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when part_type=""',
      part_type,
      ''' then part_id end) AS part_',
      part_type, '_id'
    )
  ) INTO @sql
FROM
  parts;
SET @sql = CONCAT('SELECT product_id, ', @sql, ' 
                  FROM parts 
                   GROUP BY product_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle With Demo

If you had only a few columns, then you can use a Static version:

select product_id,
  max(case when part_type="A" then part_id end) as Part_A_Id,
  max(case when part_type="B" then part_id end) as Part_B_Id
from parts
group by product_id

More Related Contents:

  1. How can I return pivot table output in MySQL?
  2. MySQL pivot row into dynamic number of columns
  3. Mysql query to dynamically convert rows to columns
  4. mysql pivot/crosstab query
  5. Mysql Convert Column to row (Pivot table )
  6. Mysql query to dynamically convert rows to columns on the basis of two columns
  7. How to transpose mysql table rows into columns
  8. Setting column values as column names in the SQL query result
  9. MySQL Pivot Table Column Data as Rows
  10. Getting the SUM() of claps and at the same time counting bookmarks in one query
  11. How to filter SQL results in a has-many-through relation
  12. Return row only if value doesn’t exist
  13. How to delete from multiple tables in MySQL?
  14. INSERT with SELECT
  15. How to get list of dates between two dates in mysql select query [duplicate]
  16. SQL order string as number
  17. Find most frequent value in SQL column
  18. How to convert number of week into date?
  19. Set NOW() as Default Value for datetime datatype?
  20. Add Auto-Increment ID to existing table?
  21. MySQL SELECT DISTINCT multiple columns
  22. SQL DELETE with JOIN another table for WHERE condition
  23. Using group by on two fields and count in SQL
  24. Can MySQL convert a stored UTC time to local timezone?
  25. MySQL CONCAT returns NULL if any field contain NULL
  26. Does Order of Fields of Multi-Column Index in MySQL Matter
  27. Alternative to using LIMIT keyword in a SubQuery in MYSQL
  28. MySQL optimizing INSERT speed being slowed down because of indices
  29. MySQL – why not index every field?
  30. Selecting distinct 2 columns combination in mysql

Leave a Comment