C# Datatable. Compare data from a column and if it matches, append the data from another column of the corresponding row

Question

For Example, If I have the following datatable,

Location	First name	Pincode	Manager
Sydney	John	123	Brian
New York	Larry	456	Sherry
Chicago	Meg	789	Linda
Dallas	Mark	012	Cooper
Sydney	Jack	123	Brian
Dallas	Chandler	012	Cooper
Sydney	Richard	123	Brian

Here, the 1st column to traverse would be Location. Wherever the Location matches, traverse all the corresponding First Names and keep it in a single row comma separated.

Location	First Name	Pincode	Manager
Sydney	John,Jack,Richard	123	Brian
New York	Larry	456	Sherry
Chicago	Meg	789	Linda
Dallas	Mark,Chandler	012	Cooper

I have stored this In a Datatable variable dt as follows:

DataTable dt = new DataTable();
using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.AppSettings["connectionString"].ToString()))
            {
                sqlConn.Open();
                using (SqlCommand cmd = new SqlCommand(script, sqlConn))
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        adapter.SelectCommand.CommandTimeout = 3 * 60;
                        adapter.Fill(dt);
                    }
                }
            }

Answer 1

If you did not have your data in Datatable format the code would be much cleaner.

Something like this should solve your problem

DataTable dt = new DataTable();
dt.Columns.Add("Location");
dt.Columns.Add("First_Name");

dt.Rows.Add("Sydney", "John");
dt.Rows.Add("New York", "Larry");
dt.Rows.Add("Chicago", "Meg");
dt.Rows.Add("Dallas", "Mark");
dt.Rows.Add("Sydney", "Jack");
dt.Rows.Add("Dallas", "Chandler");
dt.Rows.Add("Sydney", "Richard");

var result = dt
    .AsEnumerable()
    .GroupBy(x => x["Location"])
    .Select(group => new 
        { 
            Location = group.Key.ToString(), 
            First_Name = string.Join(",", group.Select(x => x["First_Name"])) 
        });

UPDATED

If you want to convert your anonymous type back to a datatable you could have a look at Best Practice: Convert LINQ Query result to a DataTable without looping

That being said. I feel if you are going this direction it would be better to do this task a different way. maybe write a stored procedure to do the job (1 db call vs 2)

If you want to do it in C# I would change the implementation of this solution to something like this

DataTable dtResult = new DataTable();
dtResult.Columns.Add("Location");
dtResult.Columns.Add("First_Name");

DataTable dt = dtResult.Clone();
dt.Rows.Add("Sydney", "John");
dt.Rows.Add("New York", "Larry");
dt.Rows.Add("Chicago", "Meg");
dt.Rows.Add("Dallas", "Mark");
dt.Rows.Add("Sydney", "Jack");
dt.Rows.Add("Dallas", "Chandler");
dt.Rows.Add("Sydney", "Richard");

var result = dt.AsEnumerable().GroupBy(x => x["Location"]) 
                 .Select(group => dtResult.Rows.Add(group.Key.ToString(),string.Join(",", group.Select(x => x["First_Name"]))));