CODEWITHSUNDEEP
c#sql-serverdatatable
SearchForKnowledge
SearchForKnowledge

Reputation: 3751

How to split a DataTable in two separate by columns

I have a DataTable which has many columns and only one row:

...
DataSet myDataSet = new DataSet();
da.Fill(myDataSet);

myDataTable = new DataTable();
myDataTable = myDataSet.Tables[0];
...

How can I split the DataSet/DataTable to have equal amount of columns (if it is an odd number, split the columns so the first DataTable has the extra column).

Scenario #1:

Main DataTable:

col1    col2    col3    col4    col5    col6    col7    col8
9       0       9       5       7       4       9       3

DataTable1:

col1    col3    col3    col4
9       0       9       5

DataTable2:

col5    col6    col7    col8
7       4       9       3

Scenario #2:

Main DataTable:

col1    col2    col3    col4    col5    col6    col7
9       0       9       5       7       4       9

DataTable1:

col1    col3    col3    col4
9       0       9       5

DataTable2:

col5    col6    col7
7       4       9

Upvotes: 3

Views: 6390

Answers (2)

Rahul
Rahul

Reputation: 77876

You can as well use the overloaded version of DataView.ToTable(String, Boolean, String[]) to achieve this passing the required column names like below

DataView view1 = new DataView(myDataSet.Tables[0]);
DataTable table1 = view1.ToTable("Table1", true, "col1", "col3", "col4", "col5", "col6");


DataView view2 = new DataView(myDataSet.Tables[0]);
DataTable table2 = view2.ToTable("Table2", true, "col2", "col7", "col8");

Upvotes: 1

Microsoft DN
Microsoft DN

Reputation: 10020

You can copy the whole data table and then remove the columns you don't want.

So for your first example, following code will return first four columns in datatable1 and remaining columns in datatable 2.

You can modify your code as per your number of columns

DataTable dataTable1;
dataTable1 = myDataTable.Copy();
dataTable1.Columns.RemoveAt(4);
dataTable1.Columns.RemoveAt(5);
dataTable1.Columns.RemoveAt(6);
dataTable1.Columns.RemoveAt(7);

DataTable dataTable2;
dataTable2 = myDataTable.Copy();
dataTable2.Columns.RemoveAt(0);
dataTable2.Columns.RemoveAt(1);
dataTable2.Columns.RemoveAt(2);
dataTable2.Columns.RemoveAt(3);

Upvotes: 2

Related Questions