Pivot Table Using Linq Entity and SQL Server 2008 R2 for Dynamic Columns

PIVOT Table

• Pivot Table Using SQL Server 2008 R2
• Pivot Table Using C# and Linq Entity

Here, I am going to explain how to do Pivot table in SQL Server 2008 R2 in a step by step manner.

Pivot Table Using SQL Server 2008 R2

We have student data like below table:

And our expected output will be like below:

To achieve this kind of pivot table, we will write a stored procedure like below:

Dynamic Pivot Table Using C# and Linq Entity

//
public DataTable GetPivotTableUsingLinqToEntity()
        {
            try
            {
                DataTable dt = new DataTable();
           
                using (var context = this.GetDataContext())
                {

		    //GetAllData() return All data for Student.
		
                    var data = context.GetAllData().ToList(); 

  		   // Student data will be like below

        //Applying linq for geeting pivot output

                    var d =   ( from f in data
                        group f by new {f.Rank, f.Pupil, f.Total}
                        into myGroup
                        where myGroup.Count() > 0
                        select new
                        {   myGroup.Key.Rank,
                            myGroup.Key.Pupil,
                            myGroup.Key.Total,
                            subject = myGroup.GroupBy(f => f.Subject).Select
                            (m => new { Sub = m.Key, Score = m.Sum(c => c.Score)})
                        }).ToList();

           // By Using GetAllSubject() Method we will Get the list of all subjects

		   var sub = context.GetAllSubject().ToList();
                 // Distinct Subject Like Below

        //Creating array for adding dynamic columns
                ArrayList objDataColumn = new ArrayList();

                if (data.Count() > 0)
                {
                    //Three column are fix "rank","pupil","Total".
                    objDataColumn.Add("Rank");
                    objDataColumn.Add("Pupil");
                    objDataColumn.Add("Total");   
                  
                    //Add Subject Name as column in Datatable
                    for (int i = 0; i < sub.Count; i++)
                    {
                        objDataColumn.Add(sub[i].SubName);
                    }
                }             
                //Add dynamic columns name to datatable dt
                for (int i = 0; i < objDataColumn.Count; i++)
                {
                    dt.Columns.Add(objDataColumn[i].ToString());
                }
            
                //Add data into datatable with respect to dynamic columns and dynamic data
                for (int i = 0; i < d.Count; i++)
                {
                    List<string> tempList = new List<string>();
                    tempList.Add(d[i].Rank.ToString());
                    tempList.Add(d[i].Pupil.ToString());
                    tempList.Add(d[i].Total.ToString());

                     var res = d[i].subject.ToList();
                     for (int j = 0; j < res.Count; j++)
                     {
                         tempList.Add(res[j].Score.ToString());
                     }

                     dt.Rows.Add(tempList.ToArray<string>());
                }
                return dt;
                //Now the Pivot datatable return like below screen

        	}
            }
            catch (Exception)
            {
                return null;
            }       
        }    
...