Click here to Skip to main content
15,071,729 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a stored sql server to get a dynamic pivot from table
now i want to display this in a view in mvc
i using asp.net and MVC4
thanks for all.

What I have tried:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using YMOReporting.Models;
using System.Data.SqlClient;

namespace YMOReporting.Controllers.Reportings
{
    public class Proc1Controller : Controller
    {
        private YMOReportingConnex db = new YMOReportingConnex();

            // GET: Proc1 : stored for a dynamic pivot
            public ActionResult Index()
            {
                return View();
            }
        public DataTable PivotTableView()
        {
            db.GetProc1().ToList();

            DataTable dt = new DataTable();

            //GetProc1() return All data for Table.
            var data = db.GetProc1().ToList();

            //Applying linq for geeting pivot output
            var d = (from f in data
                     group f by new { f.Planner, f.Vendor_Location, f.Bulk_Material }
                into myGroup
                     where myGroup.Count() > 0
                     select new
                     {
                         myGroup.Key.Planner,
                         myGroup.Key.Vendor_Location,
                         myGroup.Key.Bulk_Material,
                         subject = myGroup.GroupBy(f => f.Week).Select
                             (m => new { Sub = m.Key, Value = m.Sum(c => c.Value) })
                     }).ToList();

            var sub = db.GetProc1().ToList();
            // Distinct Week Like Below
            //Creating array for adding dynamic columns
            ArrayList objDataColumn = new ArrayList();

            if (data.Count() > 0)
            {
                //Three column are fix "Planner","Vendor_Location","Bulk_Material".
                objDataColumn.Add("Planner");
                objDataColumn.Add("Vendor_Location");
                objDataColumn.Add("Bulk_Material");

                //Add Subject Name as column in Datatable
                for (int i = 0; i < sub.Count; i++)
                {
                    objDataColumn.Add(sub[i].Week);
                }

                //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].Planner.ToString());
                    tempList.Add(d[i].Vendor_Location.ToString());
                    tempList.Add(d[i].Bulk_Material.ToString());

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

                    dt.Rows.Add(tempList.ToArray<string>());
                }

            }
            return dt;

        }

    }
}
C#

Posted
Updated 30-Nov-17 18:13pm
v3

try
public ActionResult Index()
       {
           DataTable dt = PivotTableView();
           return View(dt);
       }


@using System.Data
@model DataTable

<table cellpadding="0" cellspacing="0">
    <tr>
        @foreach (DataColumn col in Model.Columns)
        {
            <th>@col.ColumnName</th>
        }

    </tr>
    @foreach (DataRow row in Model.Rows)
    {
        <tr>
            @foreach (DataColumn col in Model.Columns)
            {
                <td>@row[col.ColumnName]</td>
            } 
        </tr>
    }
</table>
   
Comments
Elvally Boubacar 20-Nov-17 16:32pm
   
Hi thanks Sir for your a proposition but my stored return null (it return : NullReferenceException) so i can't show the result in my view
Karthik_Mahalingam 20-Nov-17 20:30pm
   
in which line you are getting the error. the view code sholuldnt since we have not accessed any property which prones to null reference error.
Elvally Boubacar 21-Nov-17 3:03am
   
in the first foreach exactly in Model.Columns
Karthik_Mahalingam 21-Nov-17 3:08am
   
is the Model null ?
Elvally Boubacar 21-Nov-17 3:27am
   
the model is for a dynamic pivot table. in below

namespace YMOReporting.Models
{
using System;
using System.Collections.Generic;
using System.Data;

public partial class Proc1_Result
{
public string Planner { get; set; }
public string Vendor_Location { get; set; }
public string Bulk_Material { get; set; }
public Nullable<double> Value { get; set; }
public string Week { get; set; }
}
}
Karthik_Mahalingam 21-Nov-17 3:33am
   
can you show a screenshot on how your data is appearing?
are you using datatable or Class as model ?
Elvally Boubacar 21-Nov-17 3:44am
   
yeh im using datatable
my view must return something as

https://drive.google.com/file/d/1dxpXch3LaM3ArKyAX-FpfGsN9WyWwPhX/view?usp=sharing
Karthik_Mahalingam 21-Nov-17 3:57am
   
The image is datable rite?
Elvally Boubacar 21-Nov-17 4:11am
   
i put it on google drive
Karthik_Mahalingam 21-Nov-17 4:12am
   
yeah saw that image, it is in datatable rite?
post datatable data you are doing some formatting?
Elvally Boubacar 21-Nov-17 4:17am
   
this is my source data who i want pivot https://drive.google.com/file/d/1NM6ZvPfTN96oHicEnI26zEU9clrRwfai/view?usp=sharing
Karthik_Mahalingam 21-Nov-17 4:19am
   
how pivot is applied?
your question is about applying pivot or dynamic table in view?
Elvally Boubacar 21-Nov-17 4:28am
   
my question:
i have a table with a dynamic column (Week) and I want to display it by pivoting this column dynamically on a mvc view
Karthik_Mahalingam 21-Nov-17 4:30am
   
how are you pivoting?
c# or javascript ?
Elvally Boubacar 21-Nov-17 4:30am
   
c#
Karthik_Mahalingam 21-Nov-17 4:30am
   
check this C# Pivot Table[^]
Elvally Boubacar 21-Nov-17 4:34am
   
can i using dynamic datatable on view with foreach to get this pivot?
Karthik_Mahalingam 21-Nov-17 5:00am
   
this is how you should do
because dynamic things cant be handled properly using class entity
Elvally Boubacar 21-Nov-17 7:15am
   
thanks for your replay
now i ask how i can dispaly this on view
for (int i = 0; i < objDataColumn.Count; i++)
                {
                    dt.Columns.Add(objDataColumn[i].ToString());
                }



for (int i = 0; i < d.Count; i++)
                {
                    DataRow dr = dt.NewRow();
                    List<string> tempList = new List<string>();
                    tempList.Add(d[i].Planner.ToString());
                    tempList.Add(d[i].Vendor_Location.ToString());
                    tempList.Add(d[i].Bulk_Material.ToString());
                    
                    var res = d[i].subject.ToList();
                    for (int j = 0; j < res.Count; j++)
                    {
                        tempList.Add(res[j].Value.ToString());
                    }
                    int  k = 0;
                    foreach(var v in tempList)
                    {
                        dr[k] = v.toString();
                        k++;
                     }
                     dt.Rows.Add(dr);
                   // dt.Rows.Add(tempList.ToArray<string>());
                } 
   
v2
Comments
Elvally Boubacar 30-Nov-17 11:49am
   
sorry a have a time
so i don't understand how can i use that can you explain more please
sir I am so sorry to send you the code for converting list to datatable as per your code.Actually the process is given bellow

1- cretate a model means a class as per the fields come from list

I modefy that in bellow

 var data = db.GetProc1().ToList();
var d = (from f in data
                     group f by new { f.Planner, f.Vendor_Location, f.Bulk_Material }
                into myGroup
                     where myGroup.Count() > 0
                     select new
                     {
                      planner =  myGroup.Key.Planner,
                      location =   myGroup.Key.Vendor_Location,
                       material =  myGroup.Key.Bulk_Material,
                         subject = myGroup.GroupBy(f => f.Week).Select
                             (m => new { Sub = m.Key, Value = m.Sum(c => c.Value) })
                     }).ToList();


here create model class in your project model

1- right click on the model folder add a class like bellow
public class planning
{
 public string planner { get; set; }
public string location { get; set;}
public string material { get; set;}
public decimal subject { get; set; }

}

public ActionResult PivotTableView()
{
 var data = db.GetProc1().ToList();             
var d = (from f in data
                     group f by new { f.Planner, f.Vendor_Location, f.Bulk_Material }
                into myGroup
                     where myGroup.Count() > 0
                     select new
                     {
                      planner =  myGroup.Key.Planner,
                      location =   myGroup.Key.Vendor_Location,
                       material =  myGroup.Key.Bulk_Material,
                         subject = myGroup.GroupBy(f => f.Week).Select
                             (m => new { Sub = m.Key, Value = m.Sum(c => c.Value) })
                     }).ToList();
   return View(d);

 }


Proces for view 

1- Right click on the method PivotTableView

2- Click the add view option from the option list
3 = tick the check box create a strolnly-typed view
4-select the model that created above as a class from the the dropdown list
5- click add

then in the view page add on he top 

<pre>@model IEnumerable<projectname.Models.classname>


in the for loop and table row display the data
   
Comments
Elvally Boubacar 4-Dec-17 8:41am
   
thanks so much sir
Elvally Boubacar 4-Dec-17 11:09am
   
soory sir but i have another question:
i want to calculate sum of all columns and all rows how can i proceed ?? thnaks
   
sir
I mean you want to count the rowwise and also coomnwise
Elvally Boubacar 5-Dec-17 3:14am
   
i want the sum of all data in every column and every row
   
sir
there are one number field others are text fields perhaps
Elvally Boubacar 6-Dec-17 4:09am
   
yeah sir i have some columns with text what can i do ? sir thanks
   
the text fields only can count . But number fields can sum

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900