Transform Row Data to Columns in Linq

Question

I have data in the variable "Result" as below

StaffName   ProjectName    Month      Weeks
Venkat      Project1       January    4
Venkat      Project1       February   2
Venkat      Project2       January    3
Kumar       Project1       March      5
Kumar       Project4       December   3

Now I want the Output Like below

StaffName   ProjectName   Jan   Feb  Mar  Dec   Total
Venkat       Project1      4     2               6
Venkat       Project2      3                     3
Kumar        Project1                 5          5
Kumar        Project4                      3     3

Please help me if you have any idea

See: http://stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq — Magnus, Feb 10 '17 at 08:34

teo van kot · Answer 1 · 2017-02-10T08:35:05.340

1

I think you want to do it in a dynamic way but there is no easy way.

So just use GroupBy, Luke. like this:

var res = data.GroupBy(x => new { x.StaffName, x.ProjectName })
    .Select(x => new
    {
        StaffName = x.Key.StaffName,
        ProjectName = x.Key.ProjectName,
        Jan = x.Where(y => y.Month == "January").Sum(y => y.Weeks),
        Feb = x.Where(y => y.Month == "February").Sum(y => y.Weeks),
        Mar = x.Where(y => y.Month == "March").Sum(y => y.Weeks),
        Dec = x.Where(y => y.Month == "December").Sum(y => y.Weeks),
        //Add more month here
        Total = x.Sum(y => y.Weeks)
    });

Here is a fiddle

edited Feb 10 '17 at 08:35

answered Feb 10 '17 at 08:26

teo van kot

12,350
10
38
70

Thank for the reply, It's working but I want to display only the months that are available in the "Result" variable. If my Result variable contains all the months we should display all the months or If my Result variable contains(Eg: Jan and Feb) it should display only those two months. – user2377494 Feb 10 '17 at 08:53
1

@user2377494 I think you would be better processing the data for every month, and only display what you want. C# works best when all properties of an object are known beforehand (in fact, most of the time that's the only option, except when you use `dynamic`). – This company is turning evil. Feb 10 '17 at 09:09

Transform Row Data to Columns in Linq

1 Answers1