You must Sign In to post a response.
  • Category: LINQ

    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
    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
    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
    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


Sign In to post your comments