Feb 1, 2013

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,',',''));

No comments:

Post a Comment