Split a DataTable into 2 or more DataTables based on Column value

Question

I have a DataTable called "DTHead" which has the following records,

 MIVID      Quantity         Value
------     ----------       --------
   1           10             3000
   1           20             3500
   1           15             2000
   2           20             3000
   2           50             7500
   3           25             2000

Here, I need to split the above DataTable into three tables based on the MIVID such as follows;

DTChild1:

  MIVID           Quantity        Value
 -------         ----------     ---------
   1                10             3000
   1                20             3500
   1                15             2000

DTChild2:

  MIVID           Quantity        Value
 -------         ----------     ---------
   2                20             3000
   2                50             7500

DTChild3:

  MIVID           Quantity        Value
 -------         ----------     ---------
    3               25             2000

Suppose, if the Header DataTable contains 4 different MIVID means, then 4 Child DataTable should be created based on the MIVID. How to do this?

cuongle · Accepted Answer · 2012-10-05T06:23:23.373

34

Use LINQ to DataTable to group the first column by GroupBy, and use method CopyToDataTable to copy list of rows to DataTable

 List<DataTable> result = DTHead.AsEnumerable()
            .GroupBy(row => row.Field<int>("MIVID"))
            .Select(g => g.CopyToDataTable())
            .ToList();

Then you can get the result as a list of DataTables as you expected.

edited Oct 05 '12 at 06:23

answered Oct 05 '12 at 06:03

cuongle

74,024
28
151
206

Sorry to dig up an old thread but, how do you think it would work if it had to be grouped by both MIVID and another field? – rgshenoy Feb 19 '15 at 08:01
2

@RohithShenoyG: You can use anonymous type, something like that: `GroupBy(row => new { Field1 = row.Field('Field1'), Field2 = row.Field('Field2')})` – cuongle Feb 21 '15 at 08:34
@Cuong Le that`s a good one, saved me a lot of time. Just needs to be " instead of ' (not trying to be a wiseguy here, you obviously know that, but probably wrote the "multiple-field comment" straight here). – śmiglidigli Mar 13 '15 at 13:55
@śmiglidigli: I am not sure that I understand what you meant, please could you explain more – cuongle Mar 13 '15 at 17:13
I am just saying you're using a `'Fieldx'` notation, which will not compile, instead of `"Fieldx"` in the comment you added about multiple fields. – śmiglidigli Mar 14 '15 at 19:29
Before I can even ask i found this... Awesome! Thanks. +1. How can I now split each result into another separated list by column? – Si8 May 16 '18 at 15:05
can you please look into one of my [question](https://stackoverflow.com/questions/55688229/how-to-split-data-table-into-multiple-tables-with-adding-1-minute-delay) related to it? – Moeez Apr 16 '19 at 06:39
Note that `CopyToDataTable()` throws an exception of `The source contains no DataRows` if the query returns no rows. – Kevin Swann Dec 17 '21 at 21:39

score 0 · Answer 2 · answered Feb 06 '19 at 08:09

0

DataTable tbl = new DataTable("Data").AsEnumerable()
    .Where(r => r.Field<int>("ParentId") == 1) // ParentId == 1
    .Where(r => r.Field<int>("Id") > 3) // Id > 3
    .Where(r => r.Field<string>("Name").Contains("L")) // Name contains L
    .OrderBy(r => r.Field<int>("Id")) // Order by Id
    .CopyToDataTable();

answered Feb 06 '19 at 08:09

Varun Tej Reddy Putchakayala

21
1

can you please look into one of my [question](https://stackoverflow.com/questions/55688229/how-to-split-data-table-into-multiple-tables-with-adding-1-minute-delay) related to it? – Moeez Apr 16 '19 at 06:43

Split a DataTable into 2 or more DataTables based on Column value

2 Answers2

Linked