A brief talk about MySQL pivot tables

part_id part_type product_id
--------------------------------------
1A1
2 B 1
3A2
4 B 2
5A3
6 B 3

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

SET @sql = NULL;
SELECT
 GROUP_CONCAT(DISTINCT
  CONCAT(
   'max(case when part_type = ''',part_type,''' then part_id end) AS part_','_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;

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