Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
390 views
in Technique[技术] by (71.8m points)

c# - How to merge rows in a DataTable when data in multiple columns match?

I am trying to figure out a good way to merge rows in a DataTable when column1, column2, column3, column4 match. Does anyone have pointers or ideas of how this can be accomplished in VB.NET and/or C#?

DataTable with duplicate rows to merge 
-------------------------------------------------------------
| Column1 | Column2 | Column3 | Column4 | Column5 | Column6 |
-------------------------------------------------------------
| 123456  | 6       | 54      | 5       | 0.00    | 36.78   |
| 123456  | 6       | 54      | 5       | 21.00   | 0.00    |
| 123456  | 6       | 54      | 8       | 0.00    | 102.09  |
| 123456  | 6       | 54      | 8       | 6.50    | 0.00    |


Final DataTable with merged rows 
-------------------------------------------------------------
| Column1 | Column2 | Column3 | Column4 | Column5 | Column6 |
-------------------------------------------------------------
| 123456  | 6       | 54      | 5       | 21.00   | 36.78   |
| 123456  | 6       | 54      | 8       | 6.50    | 102.09  |
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Here is a non-LINQ alternative. What it does is iterate each row in the first table. It then checks a secondary table to see if there are any rows in it that match the criteria. If there are, it adds the values in the other columns. If there aren't, it adds the entire row to the new table.

// Clone() only clones the table structure. It does not also clone the data.
DataTable dtFinal = dtOriginal.Clone();
for (int i = 0; i < dtOriginal.Rows.Count; i++)
{
    bool isDupe = false;
    for (int j = 0; j < dtFinal.Rows.Count; j++)
    {
        if (dtOriginal.Rows[i][0].ToString() == dtFinal.Rows[j][0].ToString()
            && dtOriginal.Rows[i][1].ToString() == dtFinal.Rows[j][1].ToString()
            && dtOriginal.Rows[i][2].ToString() == dtFinal.Rows[j][2].ToString())
        {
            dtFinal.Rows[j][3] = int.Parse(dtFinal.Rows[j][3].ToString()) + int.Parse(dtOriginal.Rows[i][3].ToString()); 
            isDupe = true;
            break;
        }
    }

    if (!isDupe)
    {
        dtFinal.ImportRow(dtOriginal.Rows[i]);
    }
}

You could expand upon this to include more/less columns in your matching criteria and your addition logic. You could probably also think of something to get rid of the column number hardcoding such as iterating them up to a specific index or something. It all depends on your requirements. This should give you a decent starting point though.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...