Select or insert from comma separated field like one value per row in Mysql

Select or insert from comma separated field like one value per row in Mysql

Sometime data are sorted in comma delimited string format in the field of a table.  We need to get data like id of the table then one value form delimited list then same Id and second value from comma separated field and so on.  Something like cross apply in sql server. I'm explaining the requirement by an example:  

Let's assume we have table say tblEmployee:

CREATE TABLE tblEmployee(

     ntEmpID BIGINT PRIMARY KEY AUTO_INCREMENT,

     vcEmpName VARCHAR(100),

     vcSkills VARCHAR(500)

);

Now I'm inserting some records into it:

INSERT INTO tblEmployee(vcEmpName,vcSkills) VALUES

     ('Scott','HTML,CSS,Java Script'),

     ('Greg','MYsq,Sql Server, Oracle,SSIS,SSRS,SSAS'),

     ('Alain','C#,Vb.net,MVC');

Data in the table is:

SELECT * FROM tblEmployee;

ntEmpID	vcEmpName	vcSkills
1	Scott	HTML,CSS,Java Script
2	Greg	MYsq,Sql Server, Oracle,SSIS,SSRS,SSAS
3	Alain	C#,Vb.net,MVC

Now I would like to get data in this format:

ntEmpID	vcEmpName	vcSkill
1	Scott	HTML
1	Scott	CSS
1	Scott	Java Script
2	Greg	MYsq
2	Greg	Sql Server
2	Greg	Oracle
2	Greg	SSIS
2	Greg	SSRS
2	Greg	SSAS
3	Alain	C#
3	Alain	Vb.net
3	Alain	MVC

Step 1: Create a new table. Say tblIndex:

CREATE TABLE tblIndex(

ntIndex INTEGER PRIMARY KEY AUTO_INCREMENT

);

Step 2: Insert some records into it according to max possible keys in the comma delimited string. In My case comma delimited keys string cannot be more than 400.  

INSERT INTO tblIndex(ntIndex)

SELECT NULL

FROM INFORMATION_SCHEMA.COLLATIONS A,

     INFORMATION_SCHEMA.COLLATIONS B  

LIMIT 400;

Step 3:

Mysql script to get data in the desire format:

SELECT

     ntEmpID,

     vcEmpName,

     SUBSTRING_INDEX(SUBSTRING_INDEX(vcSkills, ',', ntIndex), ',', -1) vcSkill

FROM tblEmployee CROSS JOIN tblIndex 

WHERE ntIndex <= 1 + LENGTH(vcSkills) - LENGTH(REPLACE(vcSkills,',',''));

Note: You can use any existing table instead of tblIndex. To insert this data into a new table write:

INSERT INTO tblEmployeeNew(ntEmpID,vcEmpName,vcSkill)

SELECT

     ntEmpID,

     vcEmpName,

     SUBSTRING_INDEX(SUBSTRING_INDEX(vcSkills, ',', ntIndex), ',', -1) vcSkill

FROM tblEmployee CROSS JOIN tblIndex 

WHERE ntIndex <= 1 + LENGTH(vcSkills) - LENGTH(REPLACE(vcSkills,',',''));