Reforming Table/Entity using SQL or Linq to Object-CodePudding

I'm trying to normalize table A which contains column Name with names concatenated and separated by "/" into table B where name are split into rows and State are copied into the new rows. I googled and tried w/o success, kindly help.

TABLE-A

Name	State
Ken/Terri/Roberto	New York
David/Ken	Chicago
Kevin/John/Mary	New Jersey

TABLE-B

Name	State
Ken	New York
Terri	New York
Roberto	New York
David	Chicago
Ken	Chicago
Kevin	New Jersey
John	New Jersey
Mary	New Jersey

CodePudding user response：

You can use combination of SelectMany(), with Select() and Split()

Input

var tableA = new []
{
    new {Name = "Ken/Terri/Roberto", State = "New York"},
    new {Name = "David/Ken", State = "New York"},
    new {Name = "Kevin/John/Mary", State = "New York"}
};

Linq

var result = tableA.SelectMany(x =>   //Flatten the given sequence
       x.Name.Split('/')              //Split each name by '/'
      .Select(y => new {Name = y, State = x.State}));  //Create Anonymous object for each name

Try it Online