Performing grouping of results in code, not database level

Question

Stackoverflowers,

I have a result set from an SQL query in the form:

Category  Column2   Column3
A         2        3.50  
A         3        2  
B         3        2  
B         1        5  
...

I need to group a result set based on a Category column and summarize the values ​​for column 2 and column3. I have to do this in the code because I cannot perform grouping in an SQL query that receives data due to the complexity of the query (long story). This grouped data will then be displayed in a table.

I work for a specific set of values ​​in the Category column, but I would like the solution to handle any possible values ​​that appear in the Category column.

I know that there should be a simple, effective way to do this, but I can't wrap my head around it right now. How would you do that?

EDIT

SQL, , , , .

, Linq .NET 3.5. - .NET 2.0, , . , ?

, , , . , ( .NET LINQ) , .

, VB.NET, - . . datareader.:

Public Class Accumulator
    Public sum1 As Integer
    Public sum2 As Decimal
End Class

If IReader.HasRows Then
    Dim grouping As New Dictionary(Of String, Accumulator)

    Do While IReader.Read
        Dim sum As New Accumulator

        If grouping.ContainsKey(IReader.GetString(0)) Then
            sum = grouping.Item(IReader.GetString(0))
        Else
            sum = New Accumulator
            grouping.Item(IReader.GetString(0)) = sum
        End If

        sum.sum1+= IReader.GetInt32(1)
        sum.sum2 += IReader.GetInt32(2)
    Loop

    For Each key As KeyValuePair(Of String, Accumulator) In grouping
        "DO WHAT YOU NEED TO DO WITH THE VALUES HERE"
    Next
End If

Answer 1

, . :

public class Accumulator
{
    public decimal SumColumn2;
    public decimal SumColumn3;
}

Dictionary<string, Accumulator> grouping = new Dictionary<string, Accumulator>;
DataTable dt = ... // this is the ungrouped DataTable
foreach (DataRow dr in dt.Rows)
{
    string category = dr["Category"];
    decimal col2 = dr["Column2"];
    decimal col3 = dr["Column3"];
    Accumulator sum = grouping[category];
    if (sum == null)
    {
        sum = new Accumulator();
        grouping[category] = sum;
    }
    sum.SumColumn2 += col2;
    sum.SumColumn3 += col3;
}

.

Answer 2

SQL-, - ( )

? SELECT ... GROUP BY ... :

SELECT Category, SUM(Column2), SUM(Column3)
FROM ( /* your query here */ )
GROUP BY Category

, , - Linq. , :

var groupedByCategory =
                    from r in results
                    group r by r.Category into g
                    select new
                    {
                        Category = g.Key,
                        SumOfColumn2 = g.Sum(x => x.Column2),
                        SumOfColumn3 = g.Sum(x => x.Column3)
                    };

---

SQL, , .

... ? ;)

, Linq .NET 3.5. - .NET 2.0, , . , ?

, . Linq .NET 3.5 .

, VS2008 , .NET 2.0, LinqBridge: Linq, .NET 3.5, # 3 ( VS2008)

Answer 3

" " , , . , reset . prev . , , .

Answer 4

DataTable DefaultView? . MSDN.

void SumDataTable() {
    MyDataTable.DefaultView.Sort = "ColumnName ASC";
    foreach( DataRowView drv in MyDataTable.DefaultView ) {
        MyDataRow dr = drv.Row as MyDataRow;    // get your DataRow type
        // results here, search in a list of custom summing classes, 
        // or use a Dictionary style approach
}

, , , , , .