locked
LINQ query for PIVOT table

    Question

  •  

    Hi all ,

     I have three tables table1, table2 and table3

     Table1                                               

     

    Id

     

    Data

    1

    Data1

    2

    Data2

    3

    Data3

     Table2

     

     

    Id

    Meta data

     

    1

    Meta data1

    2

    Meta data2

     

     

     

     

     

     

     

     Table 3

     

    Id

    Data ID

    Meta Data ID

    Value

    1

    Data1

    Metadata1

    Value1

    2

    Data1

    Metadata2

    Value2

    3

    Data2

    Metadata1

    Value3

    4

    Data2

    Metadata2

    Value4

      

    I want to create a pivot table by joining these tables using LINQ queries

     

    My result table should look like this

    Data

    Metadata 1

    Metadata2

    ‘”””   and so on……

    Data1

     

    Value1

     

    Value2

     

    Data2

    Value3

    Value4

     

     

     

     

    Please help me with the appropriate linq query which may be solution for me to achieve the result.

     Thanks in advance J

     Manohar

     

    Friday, February 20, 2009 5:33 AM

All replies

  • I wanted financial data displayed with columns showing years - in pivot form. I hope the example below helps you solve your problem.

    The table has this structure

    FinancialStatementYear
      [ID] int IDENTITY(1, 1)
      [Description] nvarchar(100)
      [ItemCategory] int
      [FinancialYear] int
      [FinancialValue] float
      [Customer]

    The LINQ Query i had to write ias as follows


    from f in FinancialStatementYears
    group f by new {f.ID, f.Description, f.ItemCategory}
    into myGroup
    where myGroup.Count() > 0
    select new
    {
        myGroup.Key.ID,
        myGroup.Key.Description,
        myGroup.Key.ItemCategory,
        Y2006 = myGroup.Where(f => f.FinancialYear == 2006).Sum(c =>c.FinancialValue),
        Y2007 = myGroup.Where(f => f.FinancialYear == 2007).Sum(c =>c.FinancialValue),
        Y2008 = myGroup.Where(f => f.FinancialYear == 2008).Sum(c =>c.FinancialValue),
        Fin = myGroup.Count()
    }


    The result is shown below (i recommend that you use linqpad to test your queries)


    OID Description ItemCategory Y2006 Y2007 Y2008 Fin

    1

    Current Assets

    0

    null null

    25000

    1

    2

    Fixed Assets

    2

    null null

    40000

    1

    3

    Current Liabilities

    1

    null null

    120000

    1

    4

    Inventory

    6

    null null

    20000

    1

    5

    Cash

    4

    null null

    12000

    1


    • Proposed as answer by Malisa Ncube Tuesday, March 24, 2009 9:54 AM
    Tuesday, March 24, 2009 9:51 AM
  • Hello,

    first of all, this solution was a great help to start !
    Thanks ...

    Having one more question ...
    (you deal with Y2006, Y2007 and Y2008 hardcoded in your Linq statement)
    Suppose I don't know how (in advance) many "years" I'm having ...
    Is there a solution to solve this in a "dynamic" way ?

    Thanks in advance for your reply,

    Kind regards,

    Johnny Driesen
    Belgium
    Thursday, April 23, 2009 8:59 AM
  • Thanks Malisa. I had some "fun" translating this into VB syntax, but your post was very helpful in getting the pivot result I needed.

    Yann
    Friday, January 29, 2010 4:15 AM
  • Can you post the results of your VB translation?
    Friday, June 18, 2010 8:03 PM
  • Hi Joshua,

    This is obviously not an exact translation of the original C# code above, but what I ended up using to get the results I needed from my own situation.

    Hopefully it'll give you enough of the syntax you need to solve your own problem.

    dim result = _
        ( _

     

            From s In shifts _
            Where _
                (s.Roster.ID = rosterID) _
                AndAlso _
                (s.Staff.ID = teamMemberID) _
            Group s By _
                  s.Date _
                , week = s.Week.ID _
                , source = s.Location.Name _
            Into shiftGroup = Group _
            Select New CurrentShiftsRow With _
                { _
                      .Week =
    CInt(week) _
                    , .Source = source _
                    , .MondayShift = shiftGroup.Where(
    Function(s) s.Day.ID = 1).FirstOrDefault() _
                    , .TuesdayShift = shiftGroup.Where(
    Function(s) s.Day.ID = 2).FirstOrDefault() _
                    , .WednesdayShift = shiftGroup.Where(
    Function(s) s.Day.ID = 3).FirstOrDefault() _
                    , .ThursdayShift = shiftGroup.Where(
    Function(s) s.Day.ID = 4).FirstOrDefault() _
                    , .FridayShift = shiftGroup.Where(
    Function(s) s.Day.ID = 5).FirstOrDefault() _
                    , .SaturdayShift = shiftGroup.Where(
    Function(s) s.Day.ID = 6).FirstOrDefault() _
                    , .SundayShift = shiftGroup.Where(
    Function(s) s.Day.ID = 7).FirstOrDefault() _
                } _
            ).ToList

    Yann

    Thursday, June 24, 2010 12:52 AM
  • Thanks...It worked!!!
    Wednesday, November 24, 2010 4:56 AM