How to merge multiple DataTable objects with group by with C# and concatinating duplicate rows?

Question

Hi I have following scenario:

I have a set of queries which will return me set of DataTable objects. The datatable will have few rows columns that are identical and few columns that are unique to each table. for example

Table 1:

Name   | Jan Data  | Feb Data
A      |10         |20
B      |12         |9

Table 2:

Name   | Mar Data  | Apr Data  | May Data
A      |12         |21         |5
C      |14         |90         |6

Now i want to merge these two data table objects. but while merging there are names such as A which is common to both tables and data varies. so i want an output such as:

Name   | Jan Data  | Feb Data  | Mar Data  | Apr Data  | May Data
A      |12         |21         |5          | 45        |52 
B      | 0         | 0         |           |62         |21
C      |14         |90         |6          |63         |42

the resultant table will have sum of each row value when grouped by name. (i did not take the trouble of calculating here apologies)

Can you suggest me an approach how this can be achieved?

I use C# 2.0

can you please elaborate your out put data how 45 for Apr Data and 52 for May Data comes — gbbosmiya
– gbbosmiya, Commented Apr 18, 2011 at 10:15
@gbbosmiya: i did not sum up properly. i ve mentioned that on the question. — Amit
– Amit, Commented Apr 18, 2011 at 11:04
@George Polevoy: We have a generic scenario where in the query we pass is not defined in compile time. It will be dynamically generated and hence we are getting different tables as results. Anyway we came up with an approach now. My colleague has already done it in sql but we had to redo it in C# because of the constraint. — Amit
– Amit, Commented Apr 18, 2011 at 11:08

duffymo · Accepted Answer · 2011-04-18 10:09:44Z

1

I'd write another query to combine the two and return exactly what I wanted. What you're describing is what relational databases and SQL are made to do. I wouldn't do it in code unless I absolutely had to.

answered Apr 18, 2011 at 10:09

duffymo

310k46 gold badges376 silver badges571 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Amit Over a year ago

that is a good idea... i m not so well versed in sql... any pointers u would like to share how this can be achieved in sql?

duffymo Over a year ago

Yes - become better versed in SQL. I can't be specific, because I don't know your schema.

Cos Callis · Accepted Answer · 2011-04-18 11:18:11Z

0

If you can do this in a view in SQL that may be your best result.

If you are constrained from doing so, I believe you will get the results you want out of DataTable.Merge

In code that will look something like:

private DataTable CombineTables(DataSet ds)
{
return New DataTable = ds.Tables(0).Merge(ds.Tables(1),true,MissingSchemaAction.Add);
}

answered Apr 18, 2011 at 11:18

Cos Callis

5,0943 gold badges33 silver badges57 bronze badges

Collectives™ on Stack Overflow

How to merge multiple DataTable objects with group by with C# and concatinating duplicate rows?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related