Example of a DataTable row-to-column in C #

Convert the data in table (1) format to the data in table (2) format. Obviously, this is a row-to-column request, I would like to go to the column in the database, because the row in the database is relatively simple, the method can refer to the site in SQL Server (row and column conversion) rows and columns and add the value and summary values, but because of other requirements, the final need to move the conversion to C #.

(table 1)

Table (2)

Not much to say, the following starts in the DataTable row to column example:

DataTable Row to Column

Private DataTable RCC (DataTable _outdatasource)

{

Read a non-repeating date line from a DataTable to construct a column for a new DataTable

DataTable distinct_date = _outdatasource.defaultview.totable (True, "date");

DataTable new_datatable = new DataTable ();

Add the Customer Name column to the new table

DataColumn New_d_col = new DataColumn ();

New_d_col. ColumnName = "Customer Name";

New_d_col. Caption = "";

NEW_DATATABLE.COLUMNS.ADD (New_d_col);

StringBuilder str_sum = new StringBuilder ();

To start constructing a date column in a new table

foreach (DataRow Dr in Distinct_date. Rows)

{

New_d_col = new DataColumn ();

New_d_col. DataType = typeof (decimal);

New_d_col. ColumnName = dr["Date"]. ToString ();

New_d_col. Caption = dr["Date"]. ToString ();

New_d_col. DefaultValue = 0;

NEW_DATATABLE.COLUMNS.ADD (New_d_col);

The purpose of this is to construct an expression for the aggregate column

Str_sum. Append ("+["). Append ("date"). Append ("]");

}

Add a total column to a new table

New_d_col = new DataColumn ();

New_d_col. DataType = typeof (decimal);

New_d_col. ColumnName = "Sum";

New_d_col. Caption = "Total";

New_d_col. DefaultValue = 0;

New_d_col. Expression = Str_sum. ToString (). substring (1);

NEW_DATATABLE.COLUMNS.ADD (New_d_col);

/* OK, now that the new table has been built, start adding data to the new table */

Read the non-duplicate customer name from the original DataTable and construct the row of the new table with the customer name as a keyword

DataTable Distinct_object = _outdatasource.defaultview.totable (true, "Customer Name");

Datarow[] DRS;

DataRow New_dr;

foreach (DataRow Dr in Distinct_object. Rows)

{

NEW_DR = New_datatable.newrow ();

new_dr["Customer Name"] = dr["Customer Name"]. ToString ();

foreach (DataRow _dr in Distinct_date. Rows)

{

DRS = _outdatasource.select ("Customer name = '" + dr["Customer Name"]. ToString () + "' and date = '" + _dr["date"]. ToString () + "'");

if (Drs. Length! = 0)

{

new_dr[_dr["Date"]. ToString ()] = Math.Round (Convert.todecimal (drs[0]["Amount"]), 2);

}

}

NEW_DATATABLE.ROWS.ADD (NEW_DR);

}

return new_datatable;

}

As you can see from the code above, we did not assign a value to the column "totals" for the new table because the column has an expression of str_sum. Append ("+["). Append ("date"). Append ("]"), so the value of this column is automatically populated.

Note that in the above expression, we added [], in the expression of the DataTable, if the column name is Chinese, be sure to add [] to the column name, or else it will be error, this is my debugging for a long time to find.

Example of a DataTable row-to-column in C #