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);
                    }
                }
            }

Welcome to StackOverflow! Please share with us what have you tried so far and where did you get stuck. — Peter Csala, Jul 19 '21 at 13:57
@PeterCsala Thank you. I am not very comfortable with DataTable operations. That is why I needed a suggestion. I have pulled this data from MS-SQL database and added it to a Datatable variable. After that I wanted to perform the action that I asked for. — Shashwat Singh, Jul 20 '21 at 06:44

score 1 · Answer 1 · answered Jul 19 '21 at 10:15

1

You need to use GroupBy the Location column and concatenate First Name Column.

Something like

_context.Locs.GroupBy(l => new { l.Location })
        .Select(g => new { g.Key.Location , Names = string.Join(",", g.Select(i => i.FirstName)) });

Have a look at this question

answered Jul 19 '21 at 10:15

Gokhan

441
2
9

Note that this is not a Linq-To-Sql or Linq-To-Entities question. So this just works with a strongly typed DataSet not with a plain DataTable. In that case use Neil's answer – Tim Schmelter Jul 19 '21 at 10:47

Neil · Accepted Answer · 2021-07-22T06:24:41.150

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"]))));

edited Jul 22 '21 at 06:24

answered Jul 19 '21 at 10:24

Neil

641
1
7
21

There is a minor thing: Even though `group` is not a [reserved keyword](https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/) it is better to avoid using it because it might cause confusion whenever one wants to convert lambda expression to query/statement expression. – Peter Csala Jul 20 '21 at 07:16
Ok. I want to store this processed in a datatable again. How do we do that? PS- The datatable contains more columns. This was just an example to simplify. Assume that value of other columns would be same for a given Location. – Shashwat Singh Jul 20 '21 at 10:44
Updated the question for better undertanding of use case. – Shashwat Singh Jul 20 '21 at 11:20
I feel like you are heading in the wrong direction then. why not use a stored procedure to do the work? 2 db calls vs 1? – Neil Jul 21 '21 at 08:52
@PeterCsala The word group is purely used in this example to help him/her understand what the group by and the select is doing. It is expected that people don't copy solution but rather understand and revise. I mean its not like he will copy the dt.rows.add(#####) – Neil Jul 21 '21 at 09:06
1

@Neil This was really helpful. Actually I was unable to explain my entire use case. This operation was just a part to simplify the other parts of the process. I got a gist of this and was able to implement it with success. Thanks a lot. – Shashwat Singh Jul 21 '21 at 13:59

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

2 Answers2