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

Question

I asked a similar question in the " Database Administrator ", but, unfortunately, no one answered this 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 line:

------------------------------------------------------ 
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 message:

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

Answer 1

It can be done using a third-party language such as C #, since you only need to run it once. And even simpler with a light ORM, for example Dapperin 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);
}