MySQL dynamic speaker

Question

I have a product parts table, for example:

<strong> 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 need a query that will return the table as follows:

 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 parts of the product

Answer 1

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

 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, you can use the 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 

Answer 2

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

Here is the previous article on how to do this.

Answer 3

just link to it twice - (you really don't have much of the question)

 select a.product_id, a.part_id "part_a_id", b.part_id "part_b_id" from parts a, parts b where a.product_id = b.product_id