As the values from the list fields in mysql to transform into a new line?

welcome!
There is a table, one column which has list of values separated by commas.
How to make a query to output each value from the list displayed in a separate row, where all the columns would be identical and varied only the value of one column?
Don't know whether it is clear explained..

Here the field - img, itemref, brand, partnumber.
In the field partnumber sometimes there is "000 180 26 09, 000 989 94 02 13ALEE, 000 989 94 02 11ALEE, 51937, 259.150.100"

I need the output:

img,itemref,brand,000 180 26 09
img,itemref,brand 000 989 94 02 13ALEE
img,itemref,brand 000 989 94 02 11ALEE
img,itemref,brand,51937
img,itemref,brand,259.150.100

thank you very much for the help!
April 7th 20 at 15:41
2 answers
April 7th 20 at 15:43
You can create a stored procedure that will do the selection.
Will create a temporary table tmp_table which will copy the structure of your table (test_procedure)

Procedure
DROP PROCEDURE IF EXISTS box;
DELIMITER //
CREATE PROCEDURE box(id INT(11))
BEGIN 
DECLARE i INT DEFAULT 3;

SET @comma= ";
SET @img := ";
SET @itemref := ";
SET @brand := ";
SET @partnumber := ";
SET @partnumber_tansform := ";

SET @tmp_field := (SELECT CONCAT(@comma := (CHAR_LENGTH(tp.partnumber) - CHAR_LENGTH(REPLACE(tp.partnumber,',',"))) div CHAR_LENGTH(','),
 @img := tp.img
 @itemref := tp.itemref,
 @brand := tp.brand,
 @partnumber := tp.partnumber)
 FROM test_procedure tp WHERE tp.id = id);

SET i = @comma;

DROP TABLE IF EXISTS `tmp_table`;
CREATE TEMPORARY TABLE `tmp_table` LIKE `test_procedure`;

WHILE i>0 DO

SET @partnumber_tansform := (SUBSTRING_INDEX(SUBSTRING_INDEX(@partnumber, ',', i), ',', -1));
INSERT INTO tmp_table(img, itemref, brand, partnumber) VALUES (@img, @itemref, @brand, @partnumber_tansform);

SET i = i-1;
END WHILE;
SELECT * FROM tmp_table;
END
//


Call so, passing the record id
CALL box(1);
April 7th 20 at 15:45
1. They were all in one column?! Deal with it!
2. If claim 1 did not fix it (import), then use a PHP script with the regular season for easy modification of the database.

Find more questions by tags MySQL