Group rows with identical value into column using LINQ

Question

I have a LINQ query which results like this.

Student  Subject    Mark 
Adam     English    80
Adam     Math       70
Adam     Science    60
Moses    English    95
Moses    Science    75

Now all I want is to convert it like following

Student English Math    Science
Adam    80      70      60
Moses   95              75

Note : Number of subjects is not fixed.

If the subjects are not fixed, then the result cannot be a class with properties. So what do you expect it to be? — Ivan Stoev, Sep 16 '16 at 13:27
Provide more code or explain what you want to do with the result or what problems you face atm. — Niklas, Sep 16 '16 at 14:11

Tim Schmelter · Answer 1 · 2016-09-16T13:15:58.133

2

If the subjects are known and fixed you could use:

var query = db.StudentMarks
    .GroupBy(x => x.Student)
    .Select(g => new {
        Student = g.Key,
        English = g.Where(x => x.Subject == "English").Sum(x=> x.Mark),
        Math    = g.Where(x => x.Subject == "Math").Sum(x=> x.Mark),
        Science = g.Where(x => x.Subject == "Science").Sum(x=> x.Mark),
    });

edited Sep 16 '16 at 13:15

answered Sep 16 '16 at 13:15

Tim Schmelter

450,073
74
686
939

You beat me to it! +1 – Rahul Tripathi Sep 16 '16 at 13:15
1

Number of subjects is not fixed. – Parvez M Robin Sep 16 '16 at 13:25
@ParvezMRobin: there is no real [`PIVOT`](https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx) operator like in sql-server. You could try an approach like this: http://stackoverflow.com/a/6282079/284240 I'd do such things in a stored-procedure, table-valued-function or view. Then you can query these functions/views with LINQ. – Tim Schmelter Sep 16 '16 at 13:28

jdweng · Accepted Answer · 2016-09-16T14:03:49.757

Try code like this

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

namespace ConsoleApplication12
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable sourceTable = new DataTable();
            sourceTable.Columns.Add("Student", typeof(string));
            sourceTable.Columns.Add("Subject", typeof(string));
            sourceTable.Columns.Add("Mark", typeof(int));

            sourceTable.Rows.Add(new object[] { "Adam","English", 80});
            sourceTable.Rows.Add(new object[] { "Adam","Math", 70});
            sourceTable.Rows.Add(new object[] { "Adam","Science", 60});
            sourceTable.Rows.Add(new object[] { "Moses","English", 95});
            sourceTable.Rows.Add(new object[] { "Moses","Science", 75});

            List<string> subjects = sourceTable.AsEnumerable().Select(x => x.Field<string>("Subject")).Distinct().ToList();
            DataTable pivotTable = new DataTable();
            pivotTable.Columns.Add("Student", typeof(string));
            foreach(string subject in subjects)
            {
                pivotTable.Columns.Add(subject, typeof(int));
            }
            var students = sourceTable.AsEnumerable()
                .GroupBy(x => x.Field<string>("Student")).ToList();

            foreach (var student in students)
            {
                DataRow newRow = pivotTable.Rows.Add();
                newRow["Student"] = student.Key;
                foreach (DataRow row in student)
                {
                    newRow[row.Field<string>("Subject")] = row.Field<int>("Mark"); 
                }

            }
        }
    }
}

score 0 · Answer 3 · answered Sep 16 '16 at 14:52

If you don't know how many subjects there will be you could collate the info into nested dictionaries as so:

var subjectMarksByStudent = new Dictionary<string, Dictionary<string, int>>();
foreach (var studentMark in studentMarks)
{
    Dictionary<string, int> subjectsAndMarksForStudent;
    if (subjectMarksByStudent.TryGetValue(studentMark.Name, out subjectsAndMarksForStudent))
    {
        subjectsAndMarksForStudent.Add(studentMark.Subject, studentMark.Mark);
    }
    else
    {
        subjectMarksByStudent.Add(studentMark.Name, new Dictionary<string, int> {{ studentMark.Subject, studentMark.Mark }});
    }
}

Group rows with identical value into column using LINQ

3 Answers3