santosh behera

santosh behera

  • NA
  • 92
  • 11.9k

How to PIVOT all rows into columns using LINQ

Feb 28 2016 8:37 PM
Hi how do i pivot rows into columns to show amount 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 records for 15 years for each invoice(data stored in database monthly)
 
Let me know if you need more clarification. 

Thanks

Answers (1)