C# LINQ pivoting on table

Question

I have following input data:

type    name    dept    salary
senior  ABC     123     2000
junior  ABC     456     1000
senior  IJK     456     3000
senior  LMN     123     2000
junior  LMN     123     1500
senior  STQ     123     2000

I desire following pivoted output:

name    dept    junior_salary   senior_salary
ABC     123     0               2000
ABC     456     1000            0
IJK     456     0               3000
LMN     123     1500            2000
STQ     123     0               2000

Following is the LINQ code I have written:

List<Posting> posting = getData();
posting.GroupBy(x=>new{x.type, x.name, x.dept})
       .Select(x=>new {
             name = x.Key.name,
             dept = x.Key.dept,
             junior_salary = x.Where(z=>x.Key.type=="junior").Sum(y=>y.salary),
             senior_salary = x.Where(z=>x.Key.type=="senior").Sum(y=>y.salary)
         }).ToList();

This is giving me incorrect results. Following are the results I am getting

name    dept    junior_salary   senior_salary
ABC     123     0               2000
ABC     456     1000            0
IJK     456     0               3000
LMN     123     0               2000
LMN     123     1500            0
STQ     123     0               2000

Here's a fiddle I have created to run the example: https://dotnetfiddle.net/QCbyyD

Need help.

Answer 1

var pivotList = posting.GroupBy(x=>new{x.name, x.dept})
   .Select(x=>new {
         name = x.Key.name,
         dept = x.Key.dept,
         junior_salary = x.Where(z=>z.type == "junior").Sum(y=>y.salary),
         senior_salary = x.Where(z=>z.type == "senior").Sum(y=>y.salary)
     }).ToList();

Fix is to remove type from group by clause.

Answer 2

This is what you need:

var pivotList =
    posting
        .GroupBy(x => new { x.name, x.dept })
        .Select(x => new
        {
            name = x.Key.name,
            dept = x.Key.dept,
            junior_salary = x.Where(z => z.type == "junior").Sum(y => y.salary),
            senior_salary = x.Where(z => z.type == "senior").Sum(y => y.salary)
        })
        .ToList();

You just shouldn't have grouped by type.