Group by a column and display all the column values linq

linq group by select multiple columns
linq group by sum multiple columns
linq group by select all columns
linq group by count multiple columns
linq group by column name c#
c# linq group by multiple levels
linq sum multiple columns
c# lambda group by multiple columns

Hello All this is my datatable input and output, I want to do it using linq Query

Input Data Table

C1 C2 C3 C4 C5 a 1 2 3 b 4 5 c 6 7 7 9 b 11 12 13 a 10 b 111

Output Data table

C1 C2 C3 C4 C5 C6 C7 a 1 2 3 10 b 4 5 11 12 13 111 c 6 7 7 9

I need to do it using linq

so first what i did is i got the max number of elements per id(C1) to add that many number of columns as you can see first i had 5 columns than due to id(c1) b i had to add two more column i did that using a combination of add range and enumerable range.

Next the main processing i was trying to directly add values using LinQ(i know its a query language and should be used as one) i was only trying

what i was thinking is to group the column C1 then display all the values in the reamining columns and join it using string.join method and than later add the data one by on

the code i used does'nt work as i am unable to dynamically get all the columns...We can get single columns string.joined value but we cannot all columns values as a string..... Any suggestion the code which i was trying to use

(From roww In DT.AsEnumerable() Group roww By id=roww.Item("ID") 
 Into gg = Group Let x=CStr(gg(0)(1))+"|"+String.Join("|",gg.Select(Function(r) CStr(r(DT.Columns.Cast(Of datacolumn).Select(Function(col) (col.columnname)))))     )
Select DT.Clone.Rows.Add(x.Split("|"c))).copytodatatable

Thank you for your time

You could do the following.

var result = dt.AsEnumerable()
                    .OrderBy(x=>x.Field<string>("C1"))
                    .GroupBy(x=>x.Field<string>("C1"))
                    .ToDictionary(x=> x.Key,y=>y.ToList().SelectMany(x=>x.ItemArray)
                    .Where(x=>x!=DBNull.Value).OfType<int>())
                    .ToCustomDataTable<int>();

Where Dictionary<string,IEnumerable<T>>.ToCustomDataTable<T> is defined as

public static class Extensions
{
    public static DataTable ToCustomDataTable<T>(this Dictionary<string,IEnumerable<T>> source)
    {
        var dt = new DataTable();
        dt.Columns.Add("C1",typeof(string));
        dt.Columns.AddRange(Enumerable.Range(2,source.Values.Select(x=>x.Count()).Max()).Select((x,index)=> new DataColumn($"C{index+2}",typeof(T))).ToArray());
        foreach(var key in source.Keys)
        {
            var values = new List<object>();
            values.Add(key);
            values.AddRange(source[key].Cast<object>());
            dt.Rows.Add(values.ToArray());
        }
        return dt;
    }
}

Demo Code

[SOLVED], Books = g.ToList() //Actually this line of is not working. Probably because Books property is type of List<string> , not List<ActualClass> . Can you� Let's try using the COUNT(distinct column) aggregate function, which counts all the different values in a column. How does this treat NULL values? SELECT COUNT(distinct salary) as "Different Salaries" FROM employee RESULTS Different Salaries 3 The query returned a "3", but there are four different salaries: 2000, 2500, 1000, and NULL.

First, i have created this list to test my code,

    var list = new List<List<string>>() { new List<string>(){"a","1","2","3" }, new List<string>(){"b","4","5" },
                  new List<string>(){"c","6","7","7","9" }, new List<string>(){"b","11","12","13" }, new List<string>(){"a","10" }, new List<string>(){"b","111" } };

Then implement the following code,

        var verticalList = list.GroupBy(x => x.First(), y=> y.Skip(1).ToList(), 
            (key,rest) => rest.Count() > 1 ? rest.Aggregate((a, b) => MyAdd(key, a, b)) : MyAdd2(key,rest.First())).ToList();

        var vertical = verticalList.Max(y => y.Count);
        var horizontal = verticalList.Count();
        var result = new string[vertical, horizontal];
        for(int i=0; i<vertical; i++) 
            for (int j = 0; j < horizontal; j++)
                if(verticalList[j].ElementAtOrDefault(i)!=null)
                result[i, j] = verticalList[j][i];


        List<string> MyAdd(string key, List<string> res, List<string> item) {
            if(!res.Contains(key))
            res.Insert(0, key);
            res.AddRange(item);
            return res;
        }
        List<string> MyAdd2(string key, List<string> res) {
            res.Insert(0, key);
            return res;
        }

result keeps the data as you want

.

If you want to see output, try with the following,
        for (int i = 0; i < horizontal; i++) {
            for (int j = 0; j < vertical; j++) {
                Console.Write(result[j, i] + " ");
            }
        Console.WriteLine("");
        }
        Console.ReadKey();

prints

a 1 2 3 10

b 4 5 11 12 13 111

c 6 7 7 9

Full code as shown below,

   public static void Main(string[] args) {
        var list = new List<List<string>>() { new List<string>(){"a","1","2","3" }, new List<string>(){"b","4","5" },
              new List<string>(){"c","6","7","7","9" }, new List<string>(){"b","11","12","13" }, new List<string>(){"a","10" }, new List<string>(){"b","111" } };

        var verticalList = list.GroupBy(x => x.First(), y => y.Skip(1).ToList(),
       (key, rest) => rest.Count() > 1 ? rest.Aggregate((a, b) => MyAdd(key, a, b)) : MyAdd2(key, rest.First())).ToList();

        var vertical = verticalList.Max(y => y.Count);
        var horizontal = verticalList.Count();
        var result = new string[vertical, horizontal];
        for (int i = 0; i < vertical; i++)
            for (int j = 0; j < horizontal; j++)
                if (verticalList[j].ElementAtOrDefault(i) != null)
                    result[i, j] = verticalList[j][i];

        List<string> MyAdd(string key, List<string> res, List<string> item)
        {
            if (!res.Contains(key))
                res.Insert(0, key);
            res.AddRange(item);
            return res;
        }
        List<string> MyAdd2(string key, List<string> res)
        {
            res.Insert(0, key);
            return res;
        }

        for (int i = 0; i < horizontal; i++)
        {
            for (int j = 0; j < vertical; j++)
            {
                Console.Write(result[j, i] + " ");
            }
            Console.WriteLine("");
        }
        Console.ReadKey();
    }

[SOLVED], This will give you one item per group: List<dynamic> data = new List<dynamic> { new {ID = 1, Message = "Hello", GroupId = 1, Date = DateTime� Group query results. 12/01/2016; 8 minutes to read +3; In this article. Grouping is one of the most powerful capabilities of LINQ. The following examples show how to group data in various ways: By a single property. By the first letter of a string property. By a computed numeric range. By Boolean predicate or other expression. By a compound key.

there are three answers in this post all are great but I found a another solution which can be done in less and simpler steps.....

SO my flow goes like this first I Read my data table(DT1) -> I also create another Data table(DT2) with just a single column ID -> I get the max number of value's an ID has using this code

CInt(String.Join("|",(System.Text.RegularExpressions.Regex.Replace( String.Join("|",(From roww In DT.AsEnumerable() Group roww By id=roww.Item("C1") Into gg = Group Let x=String.Join(",",From p In gg.AsEnumerable() Select String.Join(",",p.ItemArray.Where(Function(r) r.ToString<>"" ) )) Select x).ToList).Trim,"(?<=,)[a-zA-Z](?=,),","").Split("|"c).Select(Function(co) System.Text.RegularExpressions.Regex.Matches(co,"\d+").Count ).max)))

-->then i add that number of columns in my DT2 using Columns.Add range method -->Then the final step is i assign the DT2 with this code and the data is manipulated and inserted in to the DT2 the code is this --->

(From roww In DT.AsEnumerable() Group roww By id=roww.Item("C1") Into gg = Group Let x=System.Text.RegularExpressions.Regex.Replace(String.Join("|",(String.Join(",",From p In gg.AsEnumerable() Select String.Join(",",p.ItemArray.Where(Function(r) r.ToString<>"" )))).Split(","c). OrderByDescending(Function(o) CStr(o))),"^.+(?=[a-zA-Z])","") Select DT_Edited.Rows.Add(x.split("|"c))).copytodatatable

and by this way we successfully we get the desired output...

Thank you for your time.

Select more column from datatable with group and sum using linq , If i understand you well On the first look, you have to group data only by Idendity Name and make some changes in your query code:. Next the main processing i was trying to directly add values using LinQ(i know its a query language and should be used as one) i was only trying . what i was thinking is to group the column C1 then display all the values in the reamining columns and join it using string.join method and than later add the data one by on

Try following. I used a datatable :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using System.IO;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("C1", typeof(string));
            dt.Columns.Add("C2", typeof(int));
            dt.Columns.Add("C3", typeof(int));
            dt.Columns.Add("C4", typeof(int));
            dt.Columns.Add("C5", typeof(int));

            dt.Rows.Add(new object[] { "a", 1, 2, 3 });
            dt.Rows.Add(new object[] { "b", 4, 5 });
            dt.Rows.Add(new object[] { "c", 6, 7, 8, 9 });
            dt.Rows.Add(new object[] { "b", 11, 12, 13 });
            dt.Rows.Add(new object[] { "a", 10 });
            dt.Rows.Add(new object[] { "b", 111 });

            var groups = dt.AsEnumerable()
                .OrderBy(x => x.Field<string>("C1"))
                .GroupBy(x => x.Field<string>("C1"))
                .ToList();

            int maxColumns = groups.Select(x => x.Select(y => y.ItemArray.Where(z => z != DBNull.Value).Count() - 1).Sum()).Max();

            DataTable dt2 = new DataTable();
            dt2.Columns.Add("C1", typeof(string));

            for (int i = 1; i <= maxColumns; i++)
            {
                dt2.Columns.Add("C" + (i + 1).ToString(), typeof(int));
            }

            foreach (var group in groups)
            {
                List<object> data = new List<object>() { group.Key };
                List<object> numbers = group.SelectMany(x => x.ItemArray.Skip(1).Where(y => y != DBNull.Value).Select(y => y)).ToList();
                data.AddRange(numbers);
                dt2.Rows.Add(data.ToArray());
            }

        }
    }

}

Grouping and Aggregating Data with LINQ, The group by statement gives us this same capability in LINQ – by grouping our objects, we can perform aggregate functions like count, sum, max and min on our sets AA (List of Employee Objects with GroupCode that starts with AA) The AA and ZZ are clearly the values of the Location field we created . You can get the values from a named column in the DataTable using a single line of code in LINQ. Steps: Convert the DataTable object to Enumerable(). Apply the select clause with the column name as field name. Cast it to a string array. string [] strSummCities = dtTemp1.AsEnumerable().Select(s = > s.Field<string>(" City")).ToArray<string>();

LINQ to group by column value, I need to display a report from this table as follows. that is ,the count of each action performed by each shortname. ShortName Name1 Name2� Hi all, I would like to get the SUM value of a column using LINQ. The only thing is that I also want to get that value if it matches the ID that I will pass to the query (i.e. return the sum of the

linq query group by one column get multiple column, i want result of query group by one coloum bu selected multiple column. Hi kakasaheb kachare. As I know this may be because of the other columns fields have distinct values. 1 The example code show you group by the only one column Name. and then select All contents are copyright of their authors. The GROUP BY clause is used with the SELECT statement to make a group of rows based on the values of a specific column or expression. The SQL AGGREGATE function can be used to get summary information for every group and these are applied to an individual group.

Build a query using LINQ, Scenario 1: Apply a fixed tag to all events � Scenario 2: Apply a Devo tag based grouping the data by a time value (using group every and every ); applying select create column operation 1 as NewColumnName1 , For more examples using different operations, see the LINQ query examples section. Finally, the text in each column is converted to its numeric representation. If your file is a tab-separated file, just update the argument in the Split method to \t. Compiling the Code. Create a C# console application project, with using directives for the System.Linq and System.IO namespaces. See also. LINQ and Strings (C#) LINQ and File

Comments
  • hello @Anu Viswan can you explain to me in detail about this Where Dictionary<string,IEnumerable<T>>.ToCustomDataTable<T> is defined as as i have never defined anything when using linq i used to use simple queries without defining any.........Another thing is why cannot i use .Copytodatatable method in this query i am successfully getting the code before the .ToCustomDataTable<int>(); but after that i cannot thanks for your time
  • @Vickydas ToCustomDataTable<T> is an extension method defined as shown in the code above. You would need to add it to your solution as well.Extension methods enable you to "add" methods to existing types, without modifying the original types. In the code above, you are adding an extension method to Type Dictionary<string,IEnumerble<T>>
  • @Vickydas Please let me know if you need further clarification or help.
  • Hello @AnuViswan although your answer is great and correct but i cannot use this in my automation tool as there is no way to define a custom method (Currently).What if i create a datatable with all the necessary columns and then use this code before the .ToCustomDatatable<t> then can i use the .Copytodatatable method with the Datatable i have created Or any other way to convert it into datatable or add rows into datatable ? Thank you for your time.
  • @Vickydas what is the automation tool you are using ?
  • hello Furkan can you post the full code i am getting an error while using this code dont know why
  • I guess you get error by MyAdd and MyAdd2. You need to put them in the scope as i share above
  • Hello Furkan Ozturk thank your for your time to upload the full code..........Let me explain you the logic once what we are doing here is we are creating a dictionary with key and adding all the containing elements of that particular key together and then we can add it into our datatable as rows right ?
  • I am sorry but i didn't get whether you are talking about your logic or wanting suggestion?
  • hello Furkan i am asking about logic\