converting DataTable display data format in 2 dimensional format

Question

I've to prepare a report which shows the counts of Old Customer & New Customer on the basis of Gender. So the top level of the report is Gender then type of Customer. But the problem is in the DataTable, I am getting the plain table data format. How can I format the DataTable in 2 dimensional format.

Datatable format

required format

How would I show this report information using DataTable, GridView ?

Answer 1

Try this Code here datatable is shaped as pivot grid and bind to gridview.

ASPX:

<asp:GridView ID="gv2" OnDataBound="gv2_DataBound" runat="server" BackColor="White" BorderStyle="Solid"
        BorderWidth="1px" AutoGenerateColumns="false">
        <HeaderStyle BackColor="#95B3D7"/>           
    <Columns>
        <asp:BoundField  DataField="USER" HeaderText="USER" />
        <asp:BoundField  DataField="MALE_NEW" HeaderText="NEW CUSTOMER COUNT" ItemStyle-HorizontalAlign="Right" />
        <asp:BoundField  DataField="MALE_OLD" HeaderText="OLD CUSTOMER COUNT" ItemStyle-HorizontalAlign="Right"/>
        <asp:BoundField  DataField="FEMALE_NEW" HeaderText="NEW CUSTOMER COUNT" ItemStyle-HorizontalAlign="Right"/>
        <asp:BoundField  DataField="FEMALE_OLD" HeaderText="OLD CUSTOMER COUNT" ItemStyle-HorizontalAlign="Right"/>
    </Columns>
</asp:GridView>

CS :

    protected void Page_Load(object sender, EventArgs e)
    {
        DataTable dt = new DataTable("tbl");
        dt.Columns.Add(new DataColumn() { DataType = typeof(string), ColumnName = "USER" });
        dt.Columns.Add(new DataColumn() { DataType = typeof(string), ColumnName = "GENDER" });
        dt.Columns.Add(new DataColumn() { DataType = typeof(int), ColumnName = "NCNT" });
        dt.Columns.Add(new DataColumn() { DataType = typeof(int), ColumnName = "OCNT" });

        dt.Rows.Add("TEST", "MALE", 6, 1);
        dt.Rows.Add("TEST1", "MALE", 3);
        dt.Rows.Add("TEST2", "MALE", 4);
        dt.Rows.Add("TEST", "FEMALE", 1);
        dt.Rows.Add("TEST1", "FEMALE", 1);

        DataTable dtpivot = new DataTable();
        var ik = dt.AsEnumerable().Select(i => i.Field<string>("USER")).Distinct();
        int cnt = 0;
        foreach (var item in ik)
        {
            if (dtpivot.Columns["USER"] == null) { dtpivot.Columns.Add("USER"); }
            dtpivot.Rows.Add(item);
            var il = dt.AsEnumerable().Where(i => i.Field<string>("USER").Equals(item)).Select(j => j.Field<string>("GENDER")).Distinct();
            foreach (var item1 in il)
            {
                var im = dt.AsEnumerable().Where(i => i.Field<string>("GENDER").Equals(item1) && i.Field<string>("USER").Equals(item)).Select(j => new { newcol = j.Field<int?>("NCNT"), oldcol = j.Field<int?>("OCNT") });
                foreach (var item2 in im)
                {
                    if (dtpivot.Columns[item1 + "_NEW"] == null) { dtpivot.Columns.Add(item1 + "_NEW"); }
                    if (dtpivot.Columns[item1 + "_OLD"] == null) { dtpivot.Columns.Add(item1 + "_OLD"); }
                    dtpivot.Rows[cnt][item1 + "_NEW"] = item2.newcol;
                    dtpivot.Rows[cnt][item1 + "_OLD"] = item2.oldcol;
                }
            }
            cnt++;
        }

        gv2.DataSource = dtpivot;
        gv2.DataBind();

    }
    protected void gv2_DataBound(object sender, EventArgs e)
    {
        GridViewRow row = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Normal);

        TableHeaderCell cell = new TableHeaderCell();
        cell.BackColor = Color.White;
        row.Controls.Add(cell);

        TableHeaderCell cell1 = new TableHeaderCell();
        cell1.Text = "MALE";
        cell1.ColumnSpan = 2;
        cell1.BackColor = ColorTranslator.FromHtml("#F2DDDC");
        row.Controls.Add(cell1);

        TableHeaderCell cell2 = new TableHeaderCell();
        cell2.Text = "FEMALE";
        cell2.ColumnSpan = 2;
        cell2.BackColor = ColorTranslator.FromHtml("#F2DDDC");
        row.Controls.Add(cell2);

        gv2.HeaderRow.Parent.Controls.AddAt(0, row);
    }

in about code if we want to get column dynamically then simply remove column and from gridview and set AutoGenerateColumns="true".