C# Convert DataTable rows into Columns with custom formatting

Question

I am working on asp.net web app .net version 4. I have a data table which look like:

    FCTR_GRP    CUST_TIER   CUST_TIER
    DSC SW      Tier 1      1.000000
    DSC SW      Tier 2      1.000000
    DSC SW      Tier 3      1.000000
    DSC SW      Tier 4      1.000000

Using above datatable which I am reading from Netezza database I want to convert it into a datatable like:

    FCTR_GRP    TIER_1      TIER_2    TIER_3    TIER_4
    DSC SW      1.000000    1.000000  1.000000  1.000000

and then bind it to gridview. As tier can be added later so gridview need to be dynamic. Till now I reached till here. I am able to convert first datatable like this:

 FCTR_GRP    TIER_1      TIER_2    TIER_3    TIER_4
 DSC SW      1.000000    
 DSC SW                  1.000000  
 DSC SW                            1.000000  
 DSC SW                                      1.000000

Code Used as(Not Happy with code :( but no other option)

 DataTable CustomerCurrentFactorDataTable = new DataTable();
 CustomerCurrentFactorDataTable.Columns.Add("Factor Group", typeof(System.String));
            foreach (DataRow datarow in dt.Rows)
            {
                CustomerCurrentFactorDataTable.Columns.Add(datarow["CUST_TIER"].ToString().ToUpper(), typeof(System.String));
            }
            GridViewSample.DataSource = CustomerCurrentFactorDataTable;
            GridViewSample.DataBind();
            #region
            string query = "SELECT FCTR_GRP, CUST_TIER, CUST_FCTR FROM DBO.CUST_FCTR";
            #endregion
            dt = getData.GetDataTable(query);
            DataRow dr = CustomerCurrentFactorDataTable.NewRow();
            int columnCount = CustomerCurrentFactorDataTable.Columns.Count;
            int internalCounter = 0;
            int count = 0;
            bool flag=false;
            int loopoutcounter = 0;
            foreach (DataRow dataRow in dt.Rows)
            {
                count = 0;
                if (loopoutcounter == 2)
                {
                    loopoutcounter = 0;
                    CustomerCurrentFactorDataTable.Rows.Add(dr);
                    dr = CustomerCurrentFactorDataTable.NewRow();
                    //dr.Delete();
                }
                foreach (var cell in dataRow.ItemArray)
                {
                    if (count == 0)
                    {
                        dr[count] = cell.ToString();
                        loopoutcounter++;
                        count++;

                    }

                    if (flag)
                    {
                        dr[internalCounter] = cell.ToString();
                        flag = false;
                        loopoutcounter++;
                    }
                    foreach (DataColumn column in CustomerCurrentFactorDataTable.Columns)
                    {                            
                        if (column.ColumnName == cell.ToString().ToUpper())
                        {
                            flag = true;
                            internalCounter = column.Ordinal;
                            continue;
                        }                                               
                    }
                }

Any help will be useful if we can convert last datatable like 2nd datatable or any batter option.Thanks in Advance.

You can use LINQ and use [`.GroupBy()`](https://msdn.microsoft.com/en-us/library/bb534304(v=vs.110).aspx). This returns a grouping on `DSC SW` where the elements are all row grouped on said value. — , Dec 16 '16 at 12:42
Sounds like you need to pivot your datatable. Here's an example. http://stackoverflow.com/questions/12866685/dynamic-pivot-using-c-sharp-linq — Zath, Dec 16 '16 at 13:20

score 0 · Answer 1 · edited Jun 07 '20 at 00:51

string html = "<table>";
//add header row
html += "<tr>";
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
    html += "<td>" + ds.Tables[0].Columns[i].ColumnName + "</td>";
html += "</tr>";
//add rows
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
    html += "<tr>";
    for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
        html += "<td>" + ds.Tables[0].Rows[i][j].ToString() + "</td>";
    html += "</tr>";
}
html += "</table>";

C# Convert DataTable rows into Columns with custom formatting

1 Answers1