Merging rows from two DataTables into a new table with more columns if there is no common information.

Introduction

This started as an answer to a QA question.

If you have two DataTables:

Name      Address 
Joe       At home
Mike      Over there

Mobile    Favorite Cheese
999       Stilton
911       In a can

And you want to combine them into a single DataTable:

Name      Address      Mobile    Favorite Cheese
Joe       At home      999       Stilton
Mike      Over there   911       In a can

If they have no common information, then you have done something very wrong in your design!

If they have common information, then a simple Linq Join would do it.

But without? That's nasty...but it can be done, on a row-by-row basis

Using the code

The code isn't complex - it's just brute force and ignorance really:

        private DataTable MergeColumns(DataTable dt1, DataTable dt2)
            {
            DataTable result = new DataTable();
            foreach (DataColumn dc in dt1.Columns)
                {
                result.Columns.Add(new DataColumn(dc.ColumnName, dc.DataType));
                }
            foreach (DataColumn dc in dt2.Columns)
                {
                result.Columns.Add(new DataColumn(dc.ColumnName, dc.DataType));
                }
            for (int i = 0; i < Math.Max(dt1.Rows.Count, dt2.Rows.Count); i++)
                {
                DataRow dr = result.NewRow();
                if (i < dt1.Rows.Count)
                    {
                    for (int c = 0; c < dt1.Columns.Count; c++)
                        {
                        dr[c] = dt1.Rows[i][c];
                        }
                    }
                if (i < dt2.Rows.Count)
                    {
                    for (int c = 0; c < dt2.Columns.Count; c++)
                        {
                        dr[dt1.Columns.Count + c] = dt2.Rows[i][c];
                        }
                    }
                result.Rows.Add(dr);
                }
            return result;
            }

The only real complexity is that you need to create duplicate columns in the new table because teh framework won;t let you "copy" a column if it's already in a DataTable.

Points of Interest

The code isn't written to be that efficient - you could probably improve it quite a bit just with simple loop optimisations - but if you are combining tables which don't share common info, then you deserve some pain! :laugh:

History

2015-06-06   First version.