c# - Transpose columns to rows and date row to columns -

this asp.net mvc project. have 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 trying data in table like:

sampleskill1

type        oct     nov     dec offered     53585   52549   1036 answered    45635   45189   446 abandoned   54838   54208   630 

the closest have been able doing 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 sure not best/right way this, new linq. suggestions on how efficiently achieve desired results?

when use linq kind of things. it's not easy modify code. try these things 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