Forums - C# Corner

Forum guidelines
sunny kumar

sunny kumar

  • 1.7k
  • 140
  • 877

I want to show pivot query data in jquery data table

Mar 5 2019 3:53 AM
I have a query generated from pivot , and i want to show the data in jquery data table but not able to do so. 
 
  1. public ActionResult LoadData()  
  2.         {  
  3.             using (DHIFeedbackEntities2 Ms = new DHIFeedbackEntities2())  
  4.             {  
  5.                   
  6.                 //var summary = Ms.FeedBacks.SqlQuery("select * from [DHIFeedback].[dbo].[FeedBack]").ToList<FeedBack>();  
  7.                 var summary = Ms.FeedBacks.SqlQuery(@"SELECT *   
  8.             FROM   
  9.                 (  
  10.                 SELECT   
  11.                     YEAR([FeedBackDate])[Year],DATENAME(MONTH, [FeedBackDate])[Month],  
  12.                     COUNT(1)[FeedbackID]   
  13.                 FROM   
  14.                     [DHIFeedback].[dbo].[FeedBack]   
  15.                 GROUP BY   
  16.                     YEAR([FeedBackDate]  
  17.                 ),  
  18.                 DATENAME(MONTH, [FeedBackDate])) AS Monthlyupdate   
  19.                 PIVOT(SUM([FeedbackID]) FOR Month IN([January],[February],[March],[April],[May],  
  20.                 [June],[July],[August],[September],[October],[November],[December])) AS MNamePivot   
  21.                 order by 1,2").ToList<FeedBack>();  
  22.                 //foreach(var summ in data)  
  23.                 return Json(new { data = summary }, JsonRequestBehavior.AllowGet);  
  24.                   
  25.   
  26.             }  
  27.         }  
  1. <script type="text/javascript">  
  2.         $(document).ready(function () {  
  3.             $('#Summary').DataTable({  
  4.   
  5.                 "processing"true,  
  6.   
  7.                 "ajax": {  
  8.                     "url""/Summary/LoadData",  
  9.                     "type""GET",  
  10.                     "datatype""json",  
  11.   
  12.                 },  
  13.                 "lengthMenu": [  
  14.                     [5, 10, 25, 50, 100, -1],  
  15.                     [5, 10, 25, 50, 100, "All"]  
  16.                 ],  
  17.                 "autoWidth"true,  
  18.                 "responsive"true,  
  19.                 "lengthChange"true,  
  20.                 "ordering"true,  
  21.                 //"fnRowCallback": function (nRow, aData, iDisplayIndex) {  
  22.                 //    var oSettings = this.fnSettings();  
  23.                 //    $("td:first", nRow).html(oSettings._iDisplayStart + iDisplayIndex + 1);  
  24.                 //    return nRow;  
  25.                 //},  
  26.                 "columns": [  
  27.                       
  28.                     { "data":"Year""autoWidth"true },  
  29.                     { "data":"January""autoWidth"true },  
  30.                     { "data":"February""autoWidth"true },  
  31.                     { "data":"March""autoWidth"true },  
  32.                     { "data":"April""autoWidth"true },  
  33.                     { "data":"May""autoWidth"true },  
  34.                     { "data":"June""autoWidth"true },  
  35.                     { "data":"July""autoWidth"true },  
  36.                     { "data":"August""autoWidth"true },  
  37.                     { "data":"September""autoWidth"true },  
  38.                     { "data":"October""autoWidth"true },  
  39.                     { "data":"November""autoWidth"true },  
  40.                     { "data":"December""autoWidth"true }  
  41.                 ]  
  42.             });  
  43.         });  
  44.     </script>  
 

Answers (2)