Click here to Skip to main content
15,881,856 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
HI I am using the query

SQL
select c.name as CompanyName, b.name as BusinessUnit,d.name as DepartmentName
from Company c inner join BusinessUnit b on c.id = b.companyid inner join Department d on
 b.ID = d.BUID


which displays the recors as:


Company Business Unit Department
Samsung Phone Grand 2
Samsung Phone Note 3
Samsung TV Smart 32
Samsung Refrigerator 240 2D
Samsung Laptop NP 530 U4C
Apple Phone I Phone 5
Apple Phone I Phone 6
Apple Music Player IPod


Its fine on DB side..but on UI , I want it to be displayed as:



Company Business Unit Department
Samsung Phone Grand 2
Note 3
TV Smart 32
Refrigerator 240 2D
Laptop NP 530 U4C
Apple Phone I Phone 5
I Phone 6
Music Player IPod


Kindly Help.
Posted
Comments
Thava Rajan 12-Nov-14 3:27am    
where do you want to show your data?
in report or in a UI?
Mahatma Aladdin 12-Nov-14 3:27am    
in the UI (browser)
Laiju k 12-Nov-14 3:32am    
Are you binding on a gridView
Mahatma Aladdin 12-Nov-14 3:33am    
No. its basic html table which I i am creating on data load (dynamically).
Mukesh Pr@sad 12-Nov-14 4:35am    
you can us datatable and after adding all items u can assign it to grid

C#
public class CustomeGrid
  {
      private string Table;
      private StringBuilder TableRow;
      private StringBuilder DataColumn;
      private List<CustomeGridColumn> GridColumns;
      private DataTable dataTable;

      public CustomeGrid(DataTable dt)
      {
          //data sould be ordered in the data table
          dataTable = dt;
      }
      private void CreteTableHeader()
      {
          Table = "<table>";
          int index = 0;
          GridColumns = new List<CustomeGridColumn>();
          foreach (DataColumn col in dataTable.Columns)
          {
              Table += "<th>" + col.ColumnName + "</th>";
              GridColumns.Add(new CustomeGridColumn { Index = index, Name = col.ColumnName });
              index++;
          }

      }
      private void CreateRow()
      {
          TableRow = new StringBuilder();
          TableRow.Append("<tr>");
      }
      private void CloseRow()
      {
          TableRow.Append("</tr>");
          Table += TableRow.ToString();
      }
      private void CreateColumn(CustomeGridColumn col)
      {
          SetRowSpan(col);
          DataColumn = new StringBuilder();
          DataColumn.Append("<td #'"+col.Name+"'#>");
          DataColumn.Append("<span>");
      }
      private void CloseColumn()
      {
          DataColumn.Append("</span>");
          DataColumn.Append("</td>");
          TableRow.Append(DataColumn.ToString());
      }
      private void AddData(string data)
      {
          DataColumn.Append(data);
      }
      private void CloseTable()
      {
          Table += "</table>";
          SetRowSpanOnEndOfDataTable();
      }
      private void SetRowSpan(CustomeGridColumn col)
      {
          Table = Table.Replace("#'" + col.Name + "'#", "rowspan='" + col.RowSpan + "'");
      }
      private void SetRowSpanOnEndOfDataTable()
      {
          GridColumns.ForEach(delegate(CustomeGridColumn gc)
          {
              SetRowSpan(gc);
          });

      }

      public string GetHtmlGrid()
      {
          CreteTableHeader();
          foreach (DataRow row in dataTable.Rows)
          {
              CreateRow();
              GridColumns.ForEach(delegate(CustomeGridColumn gc)
              {
                  if (gc.PreviousValue != row[gc.Name].ToString())
                  {
                      CreateColumn(gc);
                      gc.RowSpan = 1;
                      gc.PreviousValue = row[gc.Name].ToString();
                      AddData(row[gc.Name].ToString());
                      CloseColumn();
                  }
                  else
                  gc.RowSpan++;



              });
              CloseRow();
          }
          CloseTable();

          return Table;
      }



  }
  public class CustomeGridColumn
  {
      public int Index { get; set; }
      public string Name { get; set; }
      public string PreviousValue { get; set; }
      public int RowSpan { get; set; }
  }

<
initialize the CustomeGrid class with the ordered data table from SQL query
and call the GetHtmlGrid() method in CustomeGrid class and it will =return a html string with the formated html table like the code lines below
C#
 CustomeGrid grid = new CustomeGrid(orderedDataTable);
TableContainer.InnerHtml = grid.GetHtmlGrid();
 
Share this answer
 
Why don't you use it Pivot table ? Please see my answer here.

Retrieving data from two row and showing it in one row[^]
 
Share this answer
 

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