Category: LINQ

28 Feb 2016 06:19

Joined:	11/10/2009
Level:	Silver
Points:	130

Points:	5

PIVOT rows into column using LINQ

Hi how do i pivot rows into columns and bind into a List<class> using LINQ.

below i have given some data for better understanding.

i have record in table row wise for each month wise (tbl_Invoice)
InvoiceDetailsNo InvoiceDate Amount
Invoice1 1/1/2016 1
Invoice1 1/2/2016 2
Invoice1 1/3/2016 3
Invoice1 1/4/2016 4
Invoice1 1/5/2016 5
Invoice1 1/7/2016 5
.......................
Invoice1 1/1/2017 5
Invoice1 1/2/2017 6
Invoice1 1/2/2017 7

Invoice2 1/1/2016 3
Invoice2 1/2/2016 10
Invoice2 1/3/2016 5
Invoice2 1/4/2016 11
Invoice2 1/5/2016 7
Invoice2 1/6/2016 8

i want in pivot and record will display in column wise as below.
first year column are monthly and next year onwards column will be Q1-2016(sum of amount jan, feb ,mar 2016),Q2-2016(sum of amount apr,may,june) so on..

InvoiceNo Jan 2016 Feb 2016 Mar 2016 Apr 2016 May 2016 June 2016July 2016......Q12017

Invoice1 1 2 3 4 5 0 5 18
Invoice2 3 10 5 11 7 8 0

is it possible to pivot all the rows into column in LINQ..i have record for many years(data stored in database monthly)

Thanks

#764890

29 Feb 2016 01:39

Joined:	28/04/2006
Level:	Gold
Points:	29773

Points:	2

Hai Santosh,
PIVOT is not available directly with the Linq but you can get the results by joining the table and then you can format your results in such a ways to get the pivot results.
Hope it will be helpful to you.

Regards,
Pawan Awasthi(DNS MVM)
+91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
pawansoftit@gmail.com

#766560

20 Jun 2016 22:15

Joined:	12/12/2013
Level:	Gold
Points:	529

Points:	3

Hi,

Kindly Go through the Following Links for Requirement.

1.http://stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq

2.http://stackoverflow.com/questions/963491/pivot-data-using-linq

3.http://www.codeproject.com/Tips/844709/Pivot-Table-Using-Linq-Entity-and-SQL-Server-R-for

Regards,
Karunanidhi.K

#766587

21 Jun 2016 00:54

Joined:	05/06/2009
Level:	Gold
Points:	10245

Points:	3

You can try something like like Grouby. Following is the tips to do this.


List<Invoice> myInvoiceList = GetInvoiceData();
var Myquery = myInvoiceList 
    .GroupBy(a =>a.SomeValue)
    .Select(g => new {
        Jan = g.Where(b => c.OrderDate.Month == 1).Sum(b=> c.Qty),
        Feb = g.Where(b => c.OrderDate.Month == 2).Sum(b => c.Qty),
    });

By Nathan
Direction is important than speed

PIVOT rows into column using LINQ

About dotnetspider.com

Quick Links

General

Services