sql merge rows into columns ( many to many situation )

Question

I am stuck with this sql problem :

DB : MySQL 5.6.15 storage engine : MyISAM

I have 3 tables :

1) products

id product  |  product_name    
----------  |  --------------    
 1          |  alfa    
 2          |  beta    
 3          |  gamma

2) products_materials [this is the bridge table]

id product  |  id material    
----------  |  --------------    
 1          |   1    
 1          |   2    
 1          |   3   
 2          |   1   
 3          |   1

3) materials

 id material|   material_name   
----------  |  --------------
 1          |   steel   
 2          |   gold    
 3          |   silver

I need to obtain this result :

id product  |   material_name_1  |  material_name_2  |    material_name_3    
------------|--------------------|-------------------|--------------------    
product 1     steel                   gold               silver    
product 2     gold                    null                null    
product 3     silver                   null                null

The max materials per product is 10. I had a look to pivot but i am not confident enough with it to create the right query.

Thanks a lot

are there 10 different materials in total? – Jayvee Oct 01 '16 at 08:30 — Jayvee, Oct 01 '16 at 08:30

score 0 · Answer 1 · answered Oct 01 '16 at 08:42

if there are 10 fixed products then you can do something like this:

select p.product_name,

(select m.material_name
from products p1
join products_materials pm on pm.id_product=p1.id
join materials m on m.id=pm.id_material
where m.id=1
and p1.id=p.id) as material1,

(select m.material_name
from products p1
join products_materials pm on pm.id_product=p1.id
join materials m on m.id=pm.id_material
where m.id=2
and p1.id=p.id) as material2,

(select m.material_name
from products p1
join products_materials pm on pm.id_product=p1.id
join materials m on m.id=pm.id_material
where m.id=3
and p1.id=p.id) as material3

from products p

(this example is for 3 possible products)

thanks Jayvee, this is a working solution – pietro Oct 07 '16 at 08:24 — pietro, Oct 07 '16 at 08:24

score 0 · Answer 2 · answered Oct 01 '16 at 08:50

Working out the row number in the subquery means that materia1 (for example) will always have a value (irrespective of whether it is steel , gold or silver)

 SELECT S.PRODUCT_NAME,
         MAX(CASE WHEN RN=1 THEN S.MATERIAL_NAME ELSE '' END) MATERIAL1,
         MAX(CASE WHEN RN=2 THEN S.MATERIAL_NAME ELSE '' END) MATERIAL2,
         MAX(CASE WHEN RN=3 THEN S.MATERIAL_NAME ELSE '' END) MATERIAL3,
         MAX(CASE WHEN RN=4 THEN S.MATERIAL_NAME ELSE '' END) MATERIAL4,
         MAX(CASE WHEN RN=5 THEN S.MATERIAL_NAME ELSE '' END) MATERIAL5,
         MAX(CASE WHEN RN=6 THEN S.MATERIAL_NAME ELSE '' END) MATERIAL6,
         MAX(CASE WHEN RN=7 THEN S.MATERIAL_NAME ELSE '' END) MATERIAL7,
         MAX(CASE WHEN RN=8 THEN S.MATERIAL_NAME ELSE '' END) MATERIAL8,
         MAX(CASE WHEN RN=9 THEN S.MATERIAL_NAME ELSE '' END) MATERIAL9,
         MAX(CASE WHEN RN=10 THEN S.MATERIAL_NAME ELSE '' END) MATERIAL10
FROM
(
SELECT P.PRODUCT_NAME,.M.MATERIAL_NAME,
        IF(P.ID <> @P,@RN:=1,@RN:=@RN+1) RN,
        @P:=P.ID P
FROM    (SELECT @RN:=0,@P:=0) RN,PRODUCTS P
JOIN  PRODUCTS_MATERIALS PM ON PM.PRODUCT_ID = P.ID
JOIN  MATERIALS M ON M.MATERIAL_ID = PM.MATERIAL_ID
) S
GROUP BY S.PRODUCT_NAME
ORDER BY S.PRODUCT_NAME

Thanks Salmon, this working correctly too. – pietro Oct 07 '16 at 08:24 — pietro, Oct 07 '16 at 08:24

sql merge rows into columns ( many to many situation )

2 Answers2