Transfer comma separated values in one column to different rows in MySQL

Question

I asked a similar question in "Database Administrator" but sadly no one answered the question so I decided to transfer the data.

I have a table like this:

------------------------------------------------------ 
Parts    | Owner | Number   | Item_ID | ...
------------------------------------------------------
PartB    | Adam  |    4     |   Item_a,Item_b,Item_z,...
ConD     | Steve |    2     |   Item_b,Item_c,Item_g,...

I wanted to have each value as a separate row:

------------------------------------------------------ 
Parts    | Owner | Number   | Item_ID | ...
------------------------------------------------------
PartB    | Adam  |    4     |   Item_a
PartB    | Adam  |    4     |   Item_b
PartB    | Adam  |    4     |   Item_z
ConD     | Steve |    2     |   Item_b
ConD     | Steve |    2     |   Item_c
ConD     | Steve |    2     |   Item_g

Based on this answer , I tried :

SELECT Parts, 
       Owner,
       Split.a.value('.', 'VARCHAR(100)') Item_ID
FROM   (SELECT Parts,
               Owner,
               Cast ('<M>' + Replace(Item_ID, ',', '</M><M>') + '</M>' AS XML) AS Data
        FROM  Table_1) AS A
       CROSS APPLY Data.nodes ('/M') AS Split(a) 

But I get an error:

You have an error in your SQL syntax...near '('.', 'VARCHAR(100)')... 

Answer 1

Can be done easly with a 3rd party language like C# since you only need to run it once. And even easier with a light ORM like Dapper in this example:

[Table("TableA")]
class table {
    [Key]
    int id {get; set;}
    string Parts {get; set;}
    string Owner {get; set;}
    int Number {get; set;}
    string Item_ID {get; set;}
    ... 
}

[Table("TableB")]
class newtable {
    [Key]
    int id {get; set;}
    string Parts {get; set;}
    string Owner {get; set;}
    int Number {get; set;}
    string Item_ID {get; set;}
    ... 
}

Func<SqlConnection> conn = () => new SqlConnection("Data Source=127.0.0.1; Initial Catalog=;User Id=root;Password=***");
var newdata = new List<newtable>();
foreach (var d in conn().Query<table>("Select * FROM table"))
{
    foreach (var cd in d.Item_ID.Split(','))
    {
        newdata.Add(new newtable() { Parts = d.Parts, Owner = d.Owner, Number = d.Number, Item_ID = cd.Trim(), ... });
    }
}

//insert the new datas
foreach (var data in newdata) {
    conn().Insert(data);
}