GridView Formatting in aspx

Question

I have a table in Database and I want to show this on front end with different way. but i didn't get any help to manage this and show different Way.

Database table Is :

Id  Month  Commodity  Amount
----------------------------
1   May     wheat      100
2   May     rice       200
3   June    wheat      400
4   July    maize      100

my result :

 Id  Month  Commodity  Amount
----------------------------
1   May     wheat      100
2   May     rice       200
3   June    wheat      400
4   July    maize      100

But I want to display data with gridview in below format:

  Month      wheat    rice    maize
  --------------------------------
  May        100      200     
  June       400
  July                        100

My aspx code:

<asp:GridView ID="grdData" runat="server">
</asp:GridView>

and aspx.cs code

protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            getdata();
        }
    }

    public void getdata()
    {
        using (GridFormatEntities context = new GridFormatEntities())
        {
            var result = (from r in context.tbl_Commodity
                          select new
                          {
                              Id=r.Id,
                              Month = r.Month,
                              Commodity = r.Commodity,
                              Amount = r.Amount
                          }).ToList();
            grdData.DataSource = result;
            grdData.DataBind();

        }
    }

Answer 1

What you want is to pivot your data, or a pivot table. I don't know which is the technical corect term in English. But as to your question, this has nothing to do with formatting your gridview, since you frist need to do some data aggregation, before you bind the data to your Gridview.

I have taken your basic query result as the starting point for my demonstration and then applied the pivot:

void Main()
{
    var list = new List<CustomObject>() 
    {
        new CustomObject() {Id = 1, Month ="May", Commodity ="Wheat", Amount = 100},
        new CustomObject() {Id = 2, Month ="May", Commodity ="Rice", Amount = 200},
        new CustomObject() {Id = 3, Month ="June", Commodity ="Wheat", Amount = 400},
        new CustomObject() {Id = 4, Month ="July", Commodity ="Maize", Amount = 100},
        new CustomObject() {Id = 5, Month ="August", Commodity ="Raspberry", Amount = 666},
    };

    var result = list.GroupBy (l => l.Month)
                     .Select (l => new {
                        Month = l.Key,
                        Wheat = l.Where(x => x.Commodity == "Wheat").Sum (x => x.Amount),
                        Rice = l.Where(x => x.Commodity == "Rice").Sum (x => x.Amount),
                        Maize = l.Where(x => x.Commodity == "Maize").Sum (x => x.Amount),
                        Raspberry = l.Where(x => x.Commodity == "Raspberry").Sum (x => x.Amount),
                     });

    result.Dump();
}

// Define other methods and classes here
class CustomObject
{
    public int Id { get; set; }
    public string Month { get; set; }
    public string Commodity { get; set; }
    public int Amount { get; set; }
}

Output:

If you are using LinqPad, you can download the whole query script here: http://share.linqpad.net/fqsodb.linq

/EDIT: All in all, your query should look like this:

using (GridFormatEntities context = new GridFormatEntities())
{
   var result = (from r in context.tbl_Commodity
                 select new
                 {
                     Id=r.Id,
                     Month = r.Month,
                     Commodity = r.Commodity,
                     Amount = r.Amount
                 })
                 .GroupBy (r => r.Month)
                 .Select (r => new {
                    Month = r.Key,
                    Wheat = r.Where(x => x.Commodity == "Rice").Sum (x => x.Amount),
                    Rice = r.Where(x => x.Commodity == "Rice").Sum (x => x.Amount),
                    Maize = r.Where(x => x.Commodity == "Maize").Sum (x => x.Amount),
                 }).ToList();
   grdData.DataSource = result;
   grdData.DataBind();
}