Transpose columns to rows and date row to columns

Question

This is an ASP.NET MVC project. Currently I have a table like:

Skill           Date        Offered     Answered    Abandoned
SampleSkill1    12/1/2013   53585       52549       1036
SampleSkill2    12/1/2013   7170        6997        173
SampleSkill1    11/1/2013   45635       45189       446
SampleSkill2    11/1/2013   6481        6378        103
SampleSkill1    10/1/2013   54838       54208       630
SampleSkill2    10/1/2013   7361        7235        126

I am trying to get the data in a table like:

SampleSkill1

Type        Oct     Nov     Dec
Offered     53585   52549   1036
Answered    45635   45189   446
Abandoned   54838   54208   630

The closest I have been able to get is doing something like this:

var StatsList = db.Stats.Where(c => c.Skill == "SampleSkill1").ToList();
var OfferedStatsQuery = StatsList
    .GroupBy(c => c.Skill)
    .Select(g => new StatsPivot {
        Skill = g.Key,
        Oct = g.Where(c => c.Date.Month == 10).Sum(c => c.Offered),
        Nov = g.Where(c => c.Date.Month == 11).Sum(c => c.Offered),
        Dec = g.Where(c => c.Date.Month == 12).Sum(c => c.Offered)
    });
var AnsweredStatsQuery = StatsList
    .GroupBy(c => c.Skill)
    .Select(g => new StatsPivot {
        Skill = g.Key,
        Oct = g.Where(c => c.Date.Month == 10).Sum(c => c.Answered),
        Nov = g.Where(c => c.Date.Month == 11).Sum(c => c.Answered),
        Dec = g.Where(c => c.Date.Month == 12).Sum(c => c.Answered)
    });

I am sure this is not the best/right way to do something like this, but I am new to LINQ. Any suggestions on how to efficiently achieve the desired results?

score 0 · Answer 1 · edited May 23 '17 at 11:56

0

Well my suggestion is to do it on database side (maybe through a stored procedure) instead of in code. Not very sure if this suggestion is a limitation for you because then your job becomes tremendously easy, efficient and you get a lot of options as well. Check the link here for an answer to a similar question.

Hope this helps.

edited May 23 '17 at 11:56

Community

1
1

answered Feb 06 '14 at 06:46

samar

5,021
9
47
71

score 0 · Accepted Answer · answered Feb 06 '14 at 07:20

When you use Linq for this kind of things. It's not easy to modify the code. So try to do these things from DB side..

See following examples..

http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query

http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx

Transpose columns to rows and date row to columns

2 Answers2