How to loop the List records into table using MySQL 5.7

Question

I have a stored Procedure where I'm loading Comma separated values into Table.

CREATE definer = 'root' PROCEDURE List_values_to_table(_list varchar(100))
BEGIN
DECLARE _next TEXT DEFAULT NULL;
DECLARE _nextlen INT DEFAULT NULL;
DECLARE _value INT DEFAULT NULL;
DECLARE ListName VARCHAR(64) DEFAULT NULL;
   iterator:
 LOOP
IF CHAR_LENGTH(TRIM(_list))=0 OR _list is NULL THEN
Leave iterator;
END IF;
SET _next = SUBSTRING_INDEX(_list,',',1);
SET _nextlen = CHAR_LENGHT(_next);
SET _value = TRIM(_next);
SET ListName = _value
SET _list  = INSERT(_list,1,_nextlen+1,'');
END LOOP;
END

CALL List_values_to_table (Test,Test1,Test2)

Output :

Col  
Test 
Test1 
Test2

I'm looking for a Loop where I can send multiple list

CALL List_values_to_table (Test,Test1,Test2),(Emp,Emp1,Emp2)

I'm looking for output like this.

Col  Col1  Col2
Test Test1 Test2
Emp  Emp1  Emp2

you would need to split both lists and loop over the maxnumber to get the dimensions, the question is why want you to do it in mysql. — nbk, Feb 07 '23 at 11:44
@nbk I'm working on MYSQL 5.7 Community edition Work Bench only. Why are you saying dimensions I need a Loop where split the comma separated values into Rows as displayed above — manasasai, Feb 07 '23 at 12:42
the version doesn't matter, it is for all the same. your resultset has dimension as it contains rows and columns, which have a length (number of elements) which a commonly known as dimensions — nbk, Feb 07 '23 at 12:45
@nbk I'm looking how to achieve in multiple comma separated values into table — manasasai, Feb 07 '23 at 13:25
Search for solit function in mysql and you will find multiple solutions as mysql has no build in function for it, i think i made it clear in the first comment aleady — nbk, Feb 07 '23 at 13:39
@nbk I have tried but with out any luck. Can you please throw some code suggestions for my reference — manasasai, Feb 07 '23 at 14:37
this for examle works and is easy to adept https://stackoverflow.com/a/51755170/5193536 — nbk, Feb 07 '23 at 14:57

How to loop the List records into table using MySQL 5.7

0 Answers0