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

 

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

Can you post the results of your VB translation?

Answer 5

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

Answer 6

Thanks...It worked!!!