In SQL it is very easy o convert to PIVOT however if you have a requirement to convert a data to PIVOT in C# you will find n number of links of which none are working , even if it works you will have too less control on manipulation like if you need to include a condition for Columns etc . Below code gives you PIVOT with complete control on code.
var query =
from c in Result
group c by new { c.Class, c.StudentId } into gcs
select new
{
StudentId = gcs.First().StudentId,
StudentName = gcs.First().StudentName,
Period = gcs.Select(d => d.Month),
PeriodValue = gcs.Select(d => d.Marks),
};
DataTable dataTable = new DataTable();
dataTable.Columns.Add("StudentId", typeof(string));
dataTable.Columns.Add("StudentName", typeof(string));
int columnNum = query.First().Period.Count();
foreach (var item in query.First().Period)
{
dataTable.Columns.Add(item.ToString(), typeof(string));
}
foreach (var item in query)
{
DataRow dataRow = dataTable.NewRow();
dataRow["StudentId"] = item.StudentId;
dataRow["StudentName"] = item.StudentName;
int nums = 2;
foreach (var PeriodValue in item.PeriodValue)
{
dataRow[nums++] = PeriodValue;
}
dataTable.Rows.Add(dataRow);
}
DataTable pivotTable = dataTable;
Visits: 261