Apply group by clause on Datatable in C#

The blog will explain how to retrieve a data table using C# using a group by clause.

//The way to call the Group By Function
//Argument 1 = Data table in IMAGE 1
//Argument 2 = The fields you want for the data table returned
//Argument 3 = The field you want to group by
//Argument 4 = The function you want to do It can be either SUM, COUNT, AVG etc.
DataTable dtGroupedBy = GetGroupedBy(dt, "CodeName,Quantity,Current", "CodeName", "Sum");
/// <summary>
/// The group by clause for Data Table
/// </summary>
/// <param name="dt">The main data table to be filtered with the Group By.</param>
/// <param name="columnNamesInDt">The Fields sent seperated by commas. EG "Field1,Field2,Field3"</param>
/// <param name="groupByColumnNames">The column name which should be used for group by.</param>
/// <param name="typeOfCalculation">The calculation type like Sum, Avg Or Count.</param>
/// <returns></returns>
private DataTable GetGroupedBy(DataTable dt, string columnNamesInDt, string groupByColumnNames, string typeOfCalculation)
{
    //Return its own if the column names are empty
    if (columnNamesInDt == string.Empty || groupByColumnNames == string.Empty)
    {
        return dt;
    }

    //Once the columns are added find the distinct rows and group it bu the numbet
    DataTable _dt = dt.DefaultView.ToTable(true, groupByColumnNames);

    //The column names in data table
    string[] _columnNamesInDt = columnNamesInDt.Split(',');

    for (int i = 0; i < _columnNamesInDt.Length; i = i + 1)
    {
        if (_columnNamesInDt[i] != groupByColumnNames)
        {
            _dt.Columns.Add(_columnNamesInDt[i]);
        }
    }


    //Gets the collection and send it back
    for (int i = 0; i < _dt.Rows.Count; i = i + 1)
    {
        for (int j = 0; j < _columnNamesInDt.Length; j = j + 1)
        {
            if (_columnNamesInDt[j] != groupByColumnNames)
            {
                _dt.Rows[i][j] = dt.Compute(typeOfCalculation + "(" + _columnNamesInDt[j] + ")", groupByColumnNames + " = '" + _dt.Rows[i][groupByColumnNames].ToString() + "'");
            }
        }
    }

    return _dt;
}

Screen shots to show the achievements

Image 1 :

Image 2 :

Related Blog

Apply "Multiple" column Group By on DataTable in C#

SQL 2005/2008 Query to retrieve a comma seperated single row from a table.

This query will help to combine all records in a single row result with comma seperation. I have taken both type string as well as integers.

-- Take a table variable

DECLARE @TBL as TABLE(VAL1 INT, VAL2 VARCHAR(10));

-- POPULATE THE VALUES

INSERT INTO @TBL VALUES(10,'A');

INSERT INTO @TBL VALUES(20,'B');

INSERT INTO @TBL VALUES(30,'C');

INSERT INTO @TBL VALUES(40,'D');

INSERT INTO @TBL VALUES(50,'E');

-- Show the values in tables

SELECT * FROM @TBL;



-- Declare variables

DECLARE @CommaValsInt AS VARCHAR(MAX);

DECLARE @CommaValsString AS VARCHAR(MAX);



-- Gets the sql query with Comma Seperated

SELECT 

    @CommaValsInt = COALESCE(@CommaValsInt + ',', '') + CAST(VAL1 AS VARCHAR(10)) 

    , @CommaValsString = COALESCE(@CommaValsString + ''',''', '''') + VAL2

FROM 

    @TBL;

-- Show the result

SELECT @CommaValsInt AS ResultForInt , (@CommaValsString + '''') ResultForString