combining records in same column

[schf]

I am using MySQL Version 5.1.68

 

I have been given a db that has a table which has a list of products and I have been asked to take the value where fir_id=2 from each product and append it into the value where fie_id=6  for the same pro_id.

 

So if:

CREATE TABLE details 
	(
     pro_id int, 
     fie_id int, 
     value varchar(50)
    );

INSERT INTO details 
(pro_id, fie_id, value) values 
(1,2,"Kingston2"),
(1,6,'Kingston6'),
(2,2,'Sharp'),
(2,5,'Devon 19th C'),
(2,1,'resistance'),
(2,7,'To Feb'),
(2,6,'Very late')
;

afterwards I would want

(pro_id, fie_id, value) values 
(1,6,'Kingston6,Kingston2'),
(2,5,'Devon 19th C'),
(2,1,'resistance'),
(2,7,'To Feb'),
(2,6,'Very late,Sharp')

I first thought that CONCAT would be the way forwards but then INNER JOIN. I can get it to overwrite but not append with

UPDATE details INNER JOIN
   (
    SELECT pro_id, value FROM details WHERE fie_id = 2
   ) det
ON details.pro_id = det.pro_id
SET details.value = det.value
WHERE details.fie_id = 6;

Please could anyone point me in the right direction.

 

thanks

[Barand]

UPDATE details d1

LEFT JOIN details d2 ON d1.pro_id = d2.pro_id

    AND d2.fie_id = 2

SET d1.value =

    CASE

    WHEN d2.value IS NULL THEN d1.value

    ELSE CONCAT(d1.value, ',', d2.value)

    END

WHERE d1.fie_id = 6;

[schf]

Perfect thank you