Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL dynamic-pivot

I have a table of product parts like this:

Parts

part_id      part_type      product_id
--------------------------------------
1            A              1
2            B              1
3            A              2
4            B              2
5            A              3
6            B              3

and I want a query that will return a table like this:

product_id      part_A_id      part_B_id
----------------------------------------
1               1              2
2               3              4
3               5              6

In its actual implementation there will be millions of product parts

like image 344
Vaughan Avatar asked Sep 27 '12 21:09

Vaughan


People also ask

How do I create a dynamic pivot table in MySQL?

If you already know which columns to create in pivot table, you can use a CASE statement to create a pivot table. However, to create dynamic pivot tables in MySQL, we use GROUP_CONCAT function to dynamically transpose rows to columns, as shown below.

Can pivot tables be dynamic?

You can base a pivot table on the dynamic range. Then, when you refresh the pivot table, it will include all of the data in the range, even if new rows or columns have been added.


2 Answers

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
like image 75
Taryn Avatar answered Sep 29 '22 20:09

Taryn


SQL Server has a PIVOT keyword, but with MySQL you will need to use either a lot of CASE/IF statements or a lot of JOINs.

Here is a previous post of how to do this.

like image 37
PeteGO Avatar answered Sep 29 '22 21:09

PeteGO