C# Datatable to Multi Dimensional Array

Question

I have a datatable like below. I am having a difficult time creating an easy way to group the rows like i listed. I need to be able to perform various calculations like sum all years for one id, sum all ids for one year or similar. I also need systematically assign all the years and values for one id to a set of text boxes, variables, or someway i can access them logically. I guess the biggest issue i am having is determining how to convert the datatable and what would be the best way to handle what i need to do.

Current

ID      Year        Value

1       2010        25
1       2010        25  
1       2009        30
1       2008        5   
1       2008        5       
1       2008        5       
2       2010        1
2       2010        4
2       2009        0
2       2008        25  
2       2008        25      
2       2008        25

What i Want

ID      Year        Value

1       2010        50
        2009        30  
        2008        15

2       2010        5
        2009        0
        2008        75

i Cant submit an answer yet but this is more what i was going for Thanks everyone for your help.

    var Groups =
            from DataRow row in dataset1.datatable1.Rows
            group row by row["id"] into newGroup1
            from newGroup2 in
                (from row in newGroup1
                 group row by row["year"]).Distinct()
            group newGroup2 by newGroup1.Key;

        foreach (var outerGroup in Groups)
        {
           Console.WriteLine("Level = {0}", outerGroup.Key);
            foreach (var innerGroup in outerGroup)
            {
                Console.WriteLine("Data = {0}", innerGroup.Key);
                foreach (var innerGroupElement in innerGroup)
                {
                    Console.WriteLine("\t\t{0}", innerGroupElement["Value"]);
                }
            }
        }

this gives me something like:

ID:1
    Year:2008
        Value:15
    Year:2009
        Value:30
    Year:2010
        Value:50
ID:2
    Year:2008
        Value:75
    Year:2009
        Value:0
    Year:2010
        Value:5

See Efficient DataTable Group By

YetAnotherUser
– YetAnotherUser

2012-07-25 14:48:29 +00:00
Commented Jul 25, 2012 at 14:48 — YetAnotherUser
– YetAnotherUser, Commented Jul 25, 2012 at 14:48
Have a look at grouping with LINQ

waldrumpus
– waldrumpus

2012-07-25 14:51:53 +00:00
Commented Jul 25, 2012 at 14:51 — waldrumpus
– waldrumpus, Commented Jul 25, 2012 at 14:51

Tim Schmelter · Accepted Answer · 2012-07-25 15:09:47Z

2

You can use Linq-To-DataSet and Enumerable.GroupBy:

var yearGroups = tbl.AsEnumerable()
    .GroupBy(r => new
    {
        ID = r.Field<int>("ID"),
        Year = r.Field<int>("Year")
    });

foreach (var yg in yearGroups)
{
    Console.WriteLine("ID:{0} Year:{1} Sum:{2}"
        , yg.Key.ID
        , yg.Key.Year
        , yg.Sum(r => r.Field<int>("Value")));
}

output:

ID:1 Year:2010 Sum:50
ID:1 Year:2009 Sum:30
ID:1 Year:2008 Sum:15
ID:2 Year:2010 Sum:5
ID:2 Year:2009 Sum:0
ID:2 Year:2008 Sum:75

answered Jul 25, 2012 at 15:09

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

C# Datatable to Multi Dimensional Array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related