locked
making the data values as column heading RRS feed

  • Question

  • User364607740 posted

    I have developed a LINQ query. But my requirement is to make the data values from the property "PROGRAM_NAME" as column header. 
    While doing some research I came to know that this is somewhat PIVOT TABLE, but I am knew to MVC so do not have much idea. Please see the attached image for the required output. Please note that all the values in the model class are dynamic, I can not use static column name in this PIVOT TABLE. 
    MODEL CLASSES 

    public class NikashaModels
        {
            [Key]
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public int NIKASHAId { get; set; }
            [Required]
            public decimal MAPPED_AMOUNT { get; set; }       
            
            [ForeignKey("ProgramId")]
            public ProgramModels Program { get; set; } 
            public int ProgramId { get; set; }
    
            [ForeignKey("SchoolId")]
            public SchoolModels School { get; set; }
            public int SchoolId { get; set; }
        }
    	
    public class SchoolModels 
        {
            [Key]
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public int SchoolId { get; set; }        
            public string SCHOOL_NAME { get; set; }        
        }
    	
     public class ProgramModels
        {
            [Key]
            public int ProgramId { get; set; }
            public string PROGRAM_NAME { get; set; }                 
        }

    LINQ Query

    public JsonResult SchoolNikashaRpt()
            {
                    var temp = (from n in db.Nikashas
                               join s in db.Schools on n.SchoolId equals s.SchoolId
                               join k in db.Programs on n.ProgramId equals k.ProgramId
                               orderby n.SchoolId
                               select new RptSchoolsNikashaViewModel
                               {
    							    SCHOOL_NAME = s.SCHOOL_NAME
    							   ,PROGRAM_NAME = k.PROGRAM_NAME
                                   ,MAPPED_AMOUNT = n.MAPPED_AMOUNT
                               }).ToList();
    
                    return Json(temp, JsonRequestBehavior.AllowGet);           
            }

    Attached result::

    Thursday, October 29, 2020 7:34 AM

All replies

  • User1686398519 posted

    Hi scala_1988, 

    Based on the code you provided, I wrote a simple example for you to refer to.

    Model

        public class RptSchoolsNikashaViewModel
        {
            public string SCHOOL_NAME { get; set; }
            public Dictionary<string, decimal> PROGRAM_NAMEAndMAPPED_AMOUNT { get; set; }
        }

    Controller

            public DailyMVCDemoContext db = new DailyMVCDemoContext();
            public ActionResult Index()
            {
                return View();
            }
            public JsonResult SchoolNikashaRpt()
            {
                var temp = new List<RptSchoolsNikashaViewModel>();
                var testlist = db.Nikashas.Include(m => m.Program).Include(m => m.School).ToList();
                var allPROGRAM_NAME = testlist.Select(m => m.Program.PROGRAM_NAME).Distinct().ToList();
                testlist.ForEach(t =>
                {
                    Dictionary<string, decimal> plist = new Dictionary<string, decimal>();
                    allPROGRAM_NAME.ForEach(p =>
                    {
                        plist.Add(p, 0);
                    });
                    var model = new RptSchoolsNikashaViewModel
                    {
                        SCHOOL_NAME = t.School.SCHOOL_NAME,
                        PROGRAM_NAMEAndMAPPED_AMOUNT = plist
                    };
                    if (model.PROGRAM_NAMEAndMAPPED_AMOUNT.ContainsKey(t.Program.PROGRAM_NAME))
                    {
                        model.PROGRAM_NAMEAndMAPPED_AMOUNT[t.Program.PROGRAM_NAME] = t.MAPPED_AMOUNT;
                    }
                    temp.Add(model);
                });
                return Json(temp, JsonRequestBehavior.AllowGet);
            }

    View

    <table id="area" class="table table-bordered">
        <thead>
        </thead>
        <tbody>
        </tbody>
    </table>
    @section scripts{
        <script>
        $(function () {
            $.ajax({
                "url": "@Url.Action("SchoolNikashaRpt")",
                "dataType": "json",
                success: function (data) {
                    var tableHeaders = '';
                    tableHeaders += "<tr><td>SCHOOL_NAME</td>";
                    $.each(data[0].PROGRAM_NAMEAndMAPPED_AMOUNT, function (i, val) {
                        tableHeaders += "<td>" + i + "</td>";
                    });
                    tableHeaders += "<tr></tr>";
                    console.log(tableHeaders);
                    var tableBody = '';
                    $.each(data, function (i, val) {//.PROGRAM_NAMEAndMAPPED_AMOUNT
    
                        tableBody += "<tr><td>" + val.SCHOOL_NAME + "</td>";
                        $.each(val.PROGRAM_NAMEAndMAPPED_AMOUNT, function (d, dval) {
                            tableBody += "<td>" + dval  + "</td>";
                        });
                        tableBody += "</tr>";
                    });
                    $("#area thead").empty();
                    $("#area thead").append(tableHeaders);
                    $("#area tbody").empty();
                    $("#area tbody").append(tableBody);
                }
            });
        });
        </script>   
    }

    Here is the result.

    Best Regards,

    YihuiSun

    Friday, October 30, 2020 6:22 AM
  • User-474980206 posted

    without a pivot command, in sql you sum with a case, in linq its:

      from n in db.Nikashas
           join s in db.Schools on n.SchoolId equals s.SchoolId
           join k in db.Programs on n.ProgramId equals k.ProgramId
      group new
      {
            s.SCHOOL_NAME
           ,k.PROGRAM_NAME
           ,n.MAPPED_AMOUNT
      } by SCHOOL_NAME into g
      select new 
 {
           SCHOOL_NAME = g.Key
          ,STAFF_SALARY = g.Where(s => s.PROGRAM_NAME == “STAFF SALARY”).Sum(s => s.MAPPED_AMOUNT)
          ,EQUIPMENTS = g.Where(s => s.PROGRAM_NAME == “EQUIPMENTS”).Sum(s => s.MAPPED_AMOUNT)
          ,SPORTS = g.Where(s => s.PROGRAM_NAME == “SPORTS”).Sum(s => s.MAPPED_AMOUNT)
      }).ToList();


     

    Friday, October 30, 2020 3:08 PM
  • User364607740 posted

    Thank you for your reply.

    But the PROGRAM_NAME is dynamic, I mean it's input data from user. So I can't hardcore the values. Thank You!!!

    Friday, October 30, 2020 4:00 PM
  • User-474980206 posted

    A pivot requires you know the pivot values. If you don’t know the number of columns and values, then it must be done outside linq.  I create a dictionary keyed by school ID/name. The value would be a dictionary keyed by the column header, and its value the sum.

    pretty similar to the example above.

    Saturday, October 31, 2020 12:49 AM
  • User364607740 posted

    @bruce :: I have another query. If we are using hard coded value for column names then how to create view page for such cases. Since these columns are hard coded and there is no any model which matches it. So how can I do it ?

    Tuesday, November 3, 2020 9:32 AM
  • User1686398519 posted

    Hi scala_1988, 

    According to your needs, I wrote an example, you can refer to it.

    • To facilitate the test, I used simple values.

    Model

        public class RptSchoolsNikashaViewModel
        {
            public string SCHOOL_NAME { get; set; }
            public List<string> PROGRAM_NAME { get; set; }
            public Dictionary<string, decimal> PROGRAM_NAMEAndMAPPED_AMOUNT { get; set; }
        }

    Controller

        public class Test1234Controller : Controller
        {
            public DailyMVCDemoContext db = new DailyMVCDemoContext();
            public ActionResult Index()
            {
                return View();
            }
            public JsonResult SchoolNikashaRpt()
            {
                var alldata = db.Nikashas.Include("Program").Include("School").ToList();
                var alldefaultProgramName = alldata.Select(p => p.Program.PROGRAM_NAME).Distinct().ToList();
                var temp=resetalldataGroupBySchoolName(alldataGroupBySchoolName(alldata),alldefaultProgramName);
                return Json(temp, JsonRequestBehavior.AllowGet);
            }
            //reset alldataGroupBySchoolName
            public List<RptSchoolsNikashaViewModel> resetalldataGroupBySchoolName(List<RptSchoolsNikashaViewModel> alldataGroupBySchoolName,List<string> alldefaultProgramName)
            {
                alldataGroupBySchoolName.ForEach(m =>
                {
                    m.PROGRAM_NAMEAndMAPPED_AMOUNT = alldefaultProgramNameAndValue(alldefaultProgramName);
                    m.PROGRAM_NAME.ForEach(n =>
                    {
                        var pname = n.Split('-')[0];
                        var pvalue = Convert.ToDecimal(n.Split('-')[1]);
                        if (m.PROGRAM_NAMEAndMAPPED_AMOUNT.ContainsKey(pname))
                        {
                            m.PROGRAM_NAMEAndMAPPED_AMOUNT[pname] = pvalue;
                        }
                    });
                });
                return alldataGroupBySchoolName;
            }
            //get GroupBySchoolName list
            public List<RptSchoolsNikashaViewModel> alldataGroupBySchoolName(List<NikashaModels> alldata)
            {
                var alldataGroupBySchoolName = alldata.GroupBy(m => m.School.SCHOOL_NAME)
                    .Select(g =>
                        new RptSchoolsNikashaViewModel
                        {
                            SCHOOL_NAME = g.Key,
                            PROGRAM_NAME = g.GroupBy(p => p.Program.PROGRAM_NAME).Select(t => t.Key + "-" + t.Sum(i => i.MAPPED_AMOUNT)).ToList()
                        })
                    .ToList();
                return alldataGroupBySchoolName;
            }
            //set default ProgramName And MAPPED_AMOUNT
            public Dictionary<string, decimal>  alldefaultProgramNameAndValue(List<string> alldefaultProgramName)
            {
                Dictionary<string, decimal> alldefaultProgramNameAndValue = new Dictionary<string, decimal>();
                alldefaultProgramName.ForEach(p => {
                    alldefaultProgramNameAndValue.Add(p, 0);
                });
                return alldefaultProgramNameAndValue;
            }
        }

    View(You can check the code in the previous example.)

    Here is the result.

    Best Regards,

    YihuiSun

    Wednesday, November 4, 2020 5:56 AM