Sum Value of data table rows having same cost center rows

Question

Hi Everyone,
I need a logic to implement for the below.
I have a C# console application. In that, I am generating a report. I have a requirement to sum same rows where first column value is same. I need a generic logic. Number of columns may increase and decrease. First column is fixed. Suppose first column name is cost center code. In data table, if I have 100 rows (may vary), 10 columns (May vary). if out of 100 rows , there are rows for having same cost center (first columns values), in that case need to make one rows with same cost center and other rows will columns values will be added. I am able to do that using LINQ , but in that case i have mention the column name. My requirement is column name may increase and decrease in that case, i don't know the column. what I know first column is always same and on reference of first column I have to sum of rows in data table.

eg. Input table as below.

Output table should be like this.

Answer 1

You need to group the data by the first column, and then for other column calculate the sum:

using System;
using System.Data;
using System.Linq;

namespace SampleConsoleApp
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //Get data
            var input = GetData();

            //Create empty DataTable with the same structure as input
            var result = input.Clone();

            //Reduce data, group by the first column
            //Then for each column, calculate sum
            input.Rows.Cast<DataRow>().GroupBy(
                row => row[0],
                row => row)
                .Select(group =>
                {
                    var reduced = result.NewRow();
                    reduced[0] = group.Key;
                    for (int columnIndex = 1; columnIndex < result.Columns.Count; columnIndex++)
                        reduced[columnIndex] = group.Sum(row => (double)row[columnIndex]);
                    return reduced;
                })
                .ToList()
                .ForEach(row =>
                {
                    result.Rows.Add(row);
                });

            //Print the result
            foreach (DataRow item in result.Rows)
            {
                var outputStr = string.Join(",", item.ItemArray.Select(x => x.ToString()).ToArray());
                Console.WriteLine(outputStr);
            }
            Console.ReadLine();
        }
        static DataTable GetData()
        {
            var data = new DataTable();
            data.Columns.Add("CostCenter", typeof(string));
            data.Columns.Add("A", typeof(double));
            data.Columns.Add("B", typeof(double));
            data.Columns.Add("C", typeof(double));
            data.Columns.Add("D", typeof(double));
            data.Rows.Add("Lorem", 100, 100, 100, 100);
            data.Rows.Add("Lorem", 100, 100, 100, 100);
            data.Rows.Add("Lorem", 100, 100, 100, 100);
            data.Rows.Add("Ipsum", 100, 100, 100, 100);
            data.Rows.Add("Ipsum", 100, 100, 100, 100);
            data.Rows.Add("Dolor", 100, 100, 100, 100);
            return data;
        }
    }
}

Here is a text representation of the result:

Lorem,300,300,300,300
Ipsum,200,200,200,200
Dolor,100,100,100,100