Linq able row and column conversion using Linq

Source: http://www.cnblogs.com/li-peng/

Table before conversion:

Converted table:

Detailed descriptions are provided in the Code,

Some other parameters are available below

Using System;
Using System. Collections. Generic;
Using System. Data;
Using System. Linq;
Using System. Text;

Namespace ConvertToTable
{
Class Program
{
Static void Main (string [] args)
{
# Add a table to region
DataTable _ dt = new DataTable ();
_ Dt. Columns. Add (new DataColumn ("staff_id", typeof (int) {DefaultValue = 0}); // employee id
_ Dt. Columns. Add (new DataColumn ("staff_Name", typeof (string) {DefaultValue = "1"}); // employee name
_ Dt. Columns. Add (new DataColumn ("staff_TiCheng", typeof (string) {DefaultValue = "1"}); // employee Commission Rules
_ Dt. Columns. Add (new DataColumn ("staff_TiChengAmount", typeof (double) {DefaultValue = 0}); // The number of Commissions

_ Dt. Rows. Add (1, "Xiao Li", "0 point Commission", 60 );
_ Dt. Rows. Add (1, "Xiao Li", "Reservation Commission", 70 );
_ Dt. Rows. Add (2, "Xiao Zhang", "", 500 );
_ Dt. Rows. Add (2, "John", "Reservation Commission", 60 );
_ Dt. Rows. Add (2, "Xiao Zhang", "Order Commission", 800 );
_ Dt. Rows. Add (3, "John", "0-point Commission", 30 );
_ Dt. Rows. Add (3, "John", "Order Commission", 900 );
# Endregion
// Output the original table
Console. WriteLine ("original table :");
DisplayTable (_ dt );
// Output table after row-to-column Conversion
Console. WriteLine ("converted table :");
DisplayTable (ConvertToTable (_ dt ));
Console. ReadLine ();
}

# Region conversion table
Static DataTable ConvertToTable (DataTable source)
{
DataTable dt = new DataTable ();
// The first two columns are fixed.
Dt. Columns. Add ("staff_id ");
Dt. Columns. Add ("staff_Name ");
// Convert the column with the staff_TiCheng field as the filtering condition into a graph below the row
Var columns = (from x in source. Rows. Cast <DataRow> () select x [2]. ToString (). Distinct ();
// Add the staff_TiCheng field as a new field
Foreach (var item in columns) dt. Columns. Add (item). DefaultValue = 0;
// X [1] indicates the field staff_Name. Grouping by staff_Name g indicates the information after grouping. g. Key indicates the name. If you do not understand it, query a linq group clause for grouping.
Var data = from x in source. Rows. Cast <DataRow> ()
Group x by x [1] into g
Select new {Key = g. Key. ToString (), Items = g };
Data. ToList (). ForEach (x =>
{
// Here, a string array is used. You can also use DataRow as needed.
String [] array = new string [dt. Columns. Count];
// Array [1] is the stored name
Array [1] = x. Key;
// Traverse from the second column
For (int I = 2; I <dt. Columns. Count; I ++)
{
// Array [0] Is staff_id
If (array [0] = null)
Array [0] = x. Items. ToList <DataRow> () [0] ["staff_id"]. ToString ();
// Array [0] = (from y in x. Items
// Where y [2]. ToString () = dt. Columns [I]. ToString ()
// Select y [0]. ToString (). SingleOrDefault ();
// Array [I] is a variety of Commissions
Array [I] = (from y in x. Items
Where y [2]. ToString () = dt. Columns [I]. ToString () // y [2] different Commission names are equal to the names of Columns in the table
Select y [3]. ToString () // y [3] is the amount of money that staff_TiChengAmount we are looking.
). SingleOrDefault ();
}
Dt. Rows. Add (array); // Add to table
});
Return dt;
}
/// <Summary>
/// Output table
/// </Summary>
/// <Param name = "dt"> </param>
Static void DisplayTable (DataTable dt)
{
// Output Column Title
Dt. Columns. Cast <DataColumn> (). ToList (). ForEach (x => Console. Write (x + "\ t "));
Console. WriteLine ();
// Output the information of each row
Dt. Rows. Cast <DataRow> (). ToList (). ForEach (x =>
{
X. ItemArray. ToList (). ForEach (y => Console. Write (y. ToString () + "\ t "));
Console. WriteLine ();
});
}

# Endregion
}
}

// Convert the column with the staff_TiCheng field as the filtering condition into a graph below the row

// X [1] is the field staff_Name

Y [2] the names of various commissions are equal to the names of columns in the table.

Author: Li Peng Source: Peng.