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;
            }       
        }    
...