Click here to Skip to main content
15,886,362 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a data table that has records in a format like that shown below, where the PassA and PassB fields contain comma delimited entries.

PassA   PassB   OtherField
1,2,3   1,2     X
1,3     2       Y


What recommendations do you have for generating the modified modified table that includes all permutations of the delimited entries in PassA and PassB shown below:

PassA	PassB	OtherField
1	1	X
1	2	X
2	1	X
2	2	X
3	1	X
3	2	X
1	2	Y
3	2	Y


Thanks for your consideration.
Posted
Updated 28-Aug-15 4:03am
v2

1 solution

It looks complex but it's simple really. I'll try to explain in comments:
// Test class represents your test data:
private static void Main()
{
   //UPDATE: Corrected test data type
   //Test data
   DataTable dt = new DataTable();

   dt.Columns.AddRange(new []
   {
       new DataColumn("PassA",typeof(string)),
       new DataColumn("PassB",typeof(string)),
       new DataColumn("Other",typeof(string)), 
   });

   DataRow row = dt.NewRow();
     row["PassA"] = "1,2,3";
     row["PassB"] = "1,2";
     row["Other"] = "X";
     dt.Rows.Add(row);
   row = dt.NewRow();
     row["PassA"] = "1,3";
     row["PassB"] = "2";
     row["Other"] = "Y";
     dt.Rows.Add(row);

   var fullList =
       dt.Rows.Cast<DataRow>().Select(i => new
       {
          //First select turns the string into an array
          PassA = i["PassA"].ToString().Split(','),
          PassB = i["PassB"].ToString().Split(','),
          Other = i["Other"].ToString()
       })
       //The object is now IEnumerable<anon><string[],string[],string>>
       //We need to merge the two string arrays
       .SelectMany(i =>  //We make the first array the subject, but we still want to return all results, so selectmany

            i.PassA.SelectMany(  //Same logic as before, but we'll use an overload
                  a => i.PassB,  //second subject
                 (a, b) => new {a, b, i.Other})); //select our new object type
       //By the end we have a IEnumerable<anon<string,string,string>>

  //Output for checking
  fullList.ToList().ForEach(i =&gt;
  {
    Console.WriteLine("{0}\t{1}\t{2}",i.a,i.b,i.Other);
  });
}


I hope that makes sense. if not then let me know ^_^
Andy

UPDATE: Changes test data from POCO to DataTable:
The main logic is intact. I still convert the initial object (now datatable) to anon as before
 
Share this answer
 
v4
Comments
Maciej Los 28-Aug-15 10:58am    
Andy, OP mentioned that he has datatable object...
Andy Lanng 28-Aug-15 11:29am    
Thanks - Updated
Maciej Los 28-Aug-15 11:36am    
Better, but in this line:
i["PassA"].ToString().Split(',')
ToString() method is unnecessary. Guess why? ;)

BTW: +5!
Andy Lanng 28-Aug-15 11:52am    
Thanks - didn't like it in my test. it doesn't like (object).Split() :/

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900