Asked by:
LINQ query for PIVOT table

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
Question
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)
 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
BelgiumThursday, 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.
YannFriday, 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() _
} _
).ToListYann
Thursday, June 24, 2010 12:52 AM 
Thanks...It worked!!!Wednesday, November 24, 2010 4:56 AM