using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
DataTable dt = new DataTable();
dt.Columns.Add("id");
dt.Columns.Add("code");
dt.Columns.Add("amount");
dt.Rows.Add("m1", "cash", "90");
dt.Rows.Add("m1", "nets", "50");
dt.Rows.Add("m2", "cash", "190");
dt.Rows.Add("m2", "nets", "150");
dt.Rows.Add("m2", "another", "222");
dt.Rows.Add("m3", "cash", "150");
dt.Rows.Add("m3", "another", "100");
Console.WriteLine("Source:");
DisplayTable(dt);
Console.WriteLine("Target:");
DisplayTable(ConvertDataTable(dt));
}
static DataTable ConvertDataTable(DataTable source)
{
DataTable dt = new DataTable();
dt.Columns.Add("id");
var columns = (from x in source.Rows.Cast<DataRow>() select x[1].ToString()).Distinct();
foreach (var item in columns) dt.Columns.Add(item);
var data = from x in source.Rows.Cast<DataRow>()
group x by x[0] into g
select new { Key = g.Key.ToString(), Items = g };
data.ToList().ForEach(x =>
{
string[] array = new string[dt.Columns.Count];
array[0] = x.Key;
for (int i = 1; i < dt.Columns.Count; i++)
array[i] = (from y in x.Items
where y[1].ToString() == dt.Columns[i].ToString()
select y[2].ToString())
.SingleOrDefault();
dt.Rows.Add(array);
});
return dt;
}
static void DisplayTable(DataTable dt)
{
dt.Columns.Cast<DataColumn>().ToList().ForEach(x => Console.Write(x + "\t"));
Console.WriteLine();
dt.Rows.Cast<DataRow>().ToList().ForEach(x =>
{
x.ItemArray.ToList().ForEach(y => Console.Write(y.ToString() + "\t"));
Console.WriteLine();
});
}
}
}
Source:
id code amount
m1 cash 90
m1 nets 50
m2 cash 190
m2 nets 150
m2 another 222
m3 cash 150
m3 another 100
Target:
id cash nets another
m1 90 50
m2 190 150 222
m3 150 100
Press any key to continue . . .