Two Datatable merge

Whal

i have two datatable return sql query

Table A (return sql pivot table salesid)

cid sales1 sales2 sales3 .....

001 10 30 50

002 40 60 80

Table B

cid sid Amount

001 sales1 20

001 sales2 55

001 sales3 66

002 sales1 15

....

i want to two table merge and fill datagridview

cid sales1 sales1_Amount Sales2 Sales2_Amount .....

001 10 20 30 55

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Whal12y ago
great works thx
Vulpes12y ago
I've had to make some assumptions here but try the following code ('tableA' and 'tableB' are your existing datatables):

DataTable tableC = new DataTable();
DataColumn primary = tableC.Columns.Add("cid");
tableC.PrimaryKey = new DataColumn[]{primary};

for(int i = 1; i < tableA.Columns.Count; i++)
{
string name = tableA.Columns[i].ColumnName;
tableC.Columns.Add(name, typeof(int));
name += "_Amount";
tableC.Columns.Add(name, typeof(int));
}

foreach(DataRow dr in tableA.Rows)
{
DataRow row = tableC.NewRow();
row["cid"] = dr["cid"].ToString();
for(int i = 1; i < tableA.Columns.Count; i++)
{
string name = tableA.Columns[i].ColumnName;
row[name] = Convert.ToInt32(dr[name]);
}
tableC.Rows.Add(row);
}

foreach(DataRow dr in tableB.Rows)
{
DataRow row = tableC.Rows.Find(dr["cid"].ToString());
string name = dr["sid"].ToString() + "_Amount";
row[name] = Convert.ToInt32(dr["Amount"]);
}

dataGridView1.DataSource = tableC;
Satyapriya Nayak12y ago
http://www.c-sharpcorner.com/UploadFile/gulatisa/MergeTables05122006150850PM/MergeTables.aspx