Post
Ask Question
sunny kumar

sunny kumar

  • NA
  • 333
  • 9.5k

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)