Add columns data in dataset having similar name value [Answered]RSS

• • Reply
  Rahul_agg11

  Member

  144 Points

  417 Posts

  Add columns data in dataset having similar name value

  Aug 24, 2017 05:12 AM|Rahul_agg11|LINK

  Hi Experts,

  I have to sum of the columns having similar names, my dataset is as follows, if they begin with bracket, then I need to subtract that value. My dataset as follows:-

   

  Name	A	B	C	D	E	F	G	H	Total
  A	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$20.00	$20.00
  Patient	$12.00	$0.00	$0.00	$0.00	$0.00	$0.00	$1.00	$117.00	$130.00
  B	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$152.00	$152.00
  Patient	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$36.00	$36.00
  C	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$25.00	$25.00
  Patient	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	($14.00)	($14.00)
  A	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$100.00	$100.00
  D	$0.00	$0.00	$4.00	$0.00	$60.00	$0.00	$0.00	$30.00	$94.00
  A	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$200.00	$200.00
  M	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$210.00	$347.11	$557.11
  Patient	$72.00	$0.00	$0.00	$10.00	$3.00	$0.00	$0.00	$15.00	$100.00
  S	($12.00)	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$50.50	$38.50
  A	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$20.00	$20.00
  Patient	$8.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$8.00

  I need to subtract values starting with bracket. I need output in following format:-

  Name	A	B	C	D	E	F	G	H	Total
  A	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$340.00	$340.00
  Patient	$92.00	$0.00	$0.00	$10.00	$0.00	$3.00	$1.00	$134.00	$240.00
  B	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$152.00	$152.00
  C	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$25.00	$25.00
  D	$0.00	$0.00	$4.00	$0.00	$60.00	$0.00	$0.00	$30.00	$94.00
  M	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$210.00	$347.11	$557.11
  S	($12.00)	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$50.50	$38.50

  Please Help,

  Thanks,

  Rahul

• • Reply
  Zhi Lv - MSF...

  All-Star

  45479 Points

  7008 Posts

  Microsoft

  Re: Add columns data in dataset having similar name value

  Aug 25, 2017 10:02 AM|Zhi Lv - MSFT|LINK

  Hi Rahul_agg11,

  According to your description, I don't know what the form of data in your database.
  So, I make two options, one option’s data is string and the other one is double.
  Then, you could use group by clause to group the data, and use Sum method to calculate the value. More details, please refer to the following sample code:

  In aspx

  <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" Width="50%" Height="50%">
                  <Columns>
                      <asp:BoundField DataField="Name" HeaderText="Name" HtmlEncode="False" />
                      <asp:BoundField DataField="A" HeaderText="A"  DataFormatString="{0:0.00}" />
                      <asp:BoundField DataField="B" HeaderText="B"  DataFormatString="{0:0.00}"/>
                      <asp:BoundField DataField="C" HeaderText="C"  DataFormatString="{0:0.00}"/>
                      <asp:BoundField DataField="D" HeaderText="D"  DataFormatString="{0:0.00}"/>
                      <asp:BoundField DataField="E" HeaderText="E"  DataFormatString="{0:0.00}"/>
                      <asp:BoundField DataField="F" HeaderText="F"  DataFormatString="{0:0.00}"/>
                      <asp:BoundField DataField="G" HeaderText="G"  DataFormatString="{0:0.00}"/>
                      <asp:BoundField DataField="H" HeaderText="H"  DataFormatString="{0:0.00}"/>
                      <asp:BoundField DataField="Total" HeaderText="Total" DataFormatString="{0:0.00}"/>
                  </Columns>
              </asp:GridView>
                <br /><br /><br />
              <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" Width="50%" Height="50%">
                  <Columns>
                      <asp:BoundField DataField="Name" HeaderText="Name" HtmlEncode="False" />
                      <asp:BoundField DataField="A" HeaderText="A"  DataFormatString="{0:0.00}" />
                      <asp:BoundField DataField="B" HeaderText="B"  DataFormatString="{0:0.00}"/>
                      <asp:BoundField DataField="C" HeaderText="C"  DataFormatString="{0:0.00}"/>
                      <asp:BoundField DataField="D" HeaderText="D"  DataFormatString="{0:0.00}"/>
                      <asp:BoundField DataField="E" HeaderText="E"  DataFormatString="{0:0.00}"/>
                      <asp:BoundField DataField="F" HeaderText="F"  DataFormatString="{0:0.00}"/>
                      <asp:BoundField DataField="G" HeaderText="G"  DataFormatString="{0:0.00}"/>
                      <asp:BoundField DataField="H" HeaderText="H"  DataFormatString="{0:0.00}"/>
                      <asp:BoundField DataField="Total" HeaderText="Total" DataFormatString="{0:0.00}"/>
                  </Columns>
              </asp:GridView>
  

  In code behind:

  Option 1, the data are string format:

  DataTable dt = new DataTable();
              dt.Columns.AddRange(new DataColumn[10] { new DataColumn("Name", typeof(string)), new DataColumn("A", typeof(string)), new DataColumn("B", typeof(string)), new DataColumn("C", typeof(string)), new DataColumn("D", typeof(string)), new DataColumn("E", typeof(string)), new DataColumn("F", typeof(string)), new DataColumn("G", typeof(string)), new DataColumn("H", typeof(string)), new DataColumn("Total", typeof(string)) });
              dt.Rows.Add("A", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$20.00", "$20.00");
              dt.Rows.Add("Patient", "$12.00", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$1.00", "$117.00", "$130.00");
              dt.Rows.Add("B", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$152.00", "$152.00");
              dt.Rows.Add("Patient", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$36.00", "$36.00");
              dt.Rows.Add("C", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$25.00", "$25.00");
              dt.Rows.Add("Patient", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "($14.00)", "($14.00)");
              dt.Rows.Add("A", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$100.00", "$100.00");
              dt.Rows.Add("D", "$0.00", "$0.00", "$4.00", "$0.00", "$60.00", "$0.00", "$0.00", "$30.00", "$94.00");
              dt.Rows.Add("A", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$200.00", "$200.00");
              dt.Rows.Add("M", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$210.00", "$347.11", "$557.11");
              dt.Rows.Add("Patient", "$72.00", "$0.00", "$0.00", "$10.00", "$3.00", "$0.00", "$0.00", "$15.00", "$100.00");
              dt.Rows.Add("S", "($12.00)", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$50.50", "$38.50");
              dt.Rows.Add("A", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$20.00", "$20.00");
              dt.Rows.Add("Patient", "$8.00", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00", "$8.00");
             
              var query1 = dt.AsEnumerable().GroupBy(c => c.Field<string>("Name")).Select(c => new
              {
                  Name = c.Key,
                  A = c.Sum(d => d.Field<string>("A").ToString().Contains('(') ? Convert.ToDecimal(d.Field<string>("A").ToString().Replace('(', ' ').Replace(')', ' ').Replace('$', ' ')) * -1 : Convert.ToDecimal(d.Field<string>("A").ToString().Replace('$', ' '))),
                  B = c.Sum(d => d.Field<string>("B").ToString().Contains('(') ? Convert.ToDecimal(d.Field<string>("B").ToString().Replace('(', ' ').Replace(')', ' ').Replace('$', ' ')) * -1 : Convert.ToDecimal(d.Field<string>("B").ToString().Replace('$', ' '))),
                  C = c.Sum(d => d.Field<string>("C").ToString().Contains('(') ? Convert.ToDecimal(d.Field<string>("C").ToString().Replace('(', ' ').Replace(')', ' ').Replace('$', ' ')) * -1 : Convert.ToDecimal(d.Field<string>("C").ToString().Replace('$', ' '))),
                  D = c.Sum(d => d.Field<string>("D").ToString().Contains('(') ? Convert.ToDecimal(d.Field<string>("D").ToString().Replace('(', ' ').Replace(')', ' ').Replace('$', ' ')) * -1 : Convert.ToDecimal(d.Field<string>("D").ToString().Replace('$', ' '))),
                  E = c.Sum(d => d.Field<string>("E").ToString().Contains('(') ? Convert.ToDecimal(d.Field<string>("E").ToString().Replace('(', ' ').Replace(')', ' ').Replace('$', ' ')) * -1 : Convert.ToDecimal(d.Field<string>("E").ToString().Replace('$', ' '))),
                  F = c.Sum(d => d.Field<string>("F").ToString().Contains('(') ? Convert.ToDecimal(d.Field<string>("F").ToString().Replace('(', ' ').Replace(')', ' ').Replace('$', ' ')) * -1 : Convert.ToDecimal(d.Field<string>("F").ToString().Replace('$', ' '))),
                  G = c.Sum(d => d.Field<string>("G").ToString().Contains('(') ? Convert.ToDecimal(d.Field<string>("G").ToString().Replace('(', ' ').Replace(')', ' ').Replace('$', ' ')) * -1 : Convert.ToDecimal(d.Field<string>("G").ToString().Replace('$', ' '))),
                  H = c.Sum(d => d.Field<string>("H").ToString().Contains('(') ? Convert.ToDecimal(d.Field<string>("H").ToString().Replace('(', ' ').Replace(')', ' ').Replace('$', ' ')) * -1 : Convert.ToDecimal(d.Field<string>("H").ToString().Replace('$', ' '))),
                  Total = c.Sum(d => d.Field<string>("Total").ToString().Contains('(') ? Convert.ToDecimal(d.Field<string>("Total").ToString().Replace('(', ' ').Replace(')', ' ').Replace('$', ' ')) * -1 : Convert.ToDecimal(d.Field<string>("Total").ToString().Replace('$', ' '))),
              });
              GridView2.DataSource = dt;
              GridView2.DataBind();
              foreach (GridViewRow row in GridView2.Rows)
              {
                  for (int i = 0; i < row.Cells.Count; i++)
                  {
                      if (row.Cells[i].Text.Contains('('))
                      {
                          row.Cells[i].ForeColor = Color.Red;
                      }
                  }
              }
              GridView1.DataSource = query1.ToList();
              GridView1.DataBind();
              foreach (GridViewRow row in GridView1.Rows)
              {
                  for (int i = 0; i < GridView1.Columns.Count; i++)
                  {
                      row.Cells[i].Text = '$'+row.Cells[i].Text;
                      if (row.Cells[i].Text.Contains('-'))
                      {
                          row.Cells[i].Text = row.Cells[i].Text.Replace('-',' ');
                          row.Cells[i].Text = '(' + row.Cells[i].Text;
                          row.Cells[i].Text += ')';
                      }
                      if (row.Cells[i].Text.Contains('('))
                      {
                          row.Cells[i].ForeColor = Color.Red;
                      }
                  }
              }
  

  Option 2, the data are double type:

  DataTable dt2 = new DataTable();
              dt2.Columns.AddRange(new DataColumn[10] { new DataColumn("Name", typeof(string)), new DataColumn("A", typeof(double)), new DataColumn("B", typeof(double)), new DataColumn("C", typeof(double)), new DataColumn("D", typeof(double)), new DataColumn("E", typeof(double)), new DataColumn("F", typeof(double)), new DataColumn("G", typeof(double)), new DataColumn("H", typeof(double)), new DataColumn("Total", typeof(double)) });
              dt2.Rows.Add("A", 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 20.00, 20.00);
              dt2.Rows.Add("Patient", 12.00, 0.00, 0.00, 0.00, 0.00, 0.00, 1.00, 117.00, 130.00);
              dt2.Rows.Add("B", 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 152.00, 152.00);
              dt2.Rows.Add("Patient", 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 36.00, 36.00);
              dt2.Rows.Add("C", 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 25.00, 25.00);
              dt2.Rows.Add("Patient", 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, -14.00, -14.00);
              dt2.Rows.Add("A", 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 100.00, 100.00);
              dt2.Rows.Add("D", 0.00, 0.00, 4.00, 0.00, 60.00, 0.00, 0.00, 30.00, 94.00);
              dt2.Rows.Add("A", 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 200.00, 200.00);
              dt2.Rows.Add("M", 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 210.00, 347.11, 557.11);
              dt2.Rows.Add("Patient", 72.00, 0.00, 0.00, 10.00, 3.00, 0.00, 0.00, 15.00, 100.00);
              dt2.Rows.Add("S", -12.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 50.50, 38.50);
              dt2.Rows.Add("A", 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 20.00, 20.00);
              dt2.Rows.Add("Patient", 8.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 8.00);
              GridView2.DataSource = dt2;
              GridView2.DataBind();
              foreach (GridViewRow row in GridView2.Rows)
              {
                  for (int i = 0; i < GridView2.Columns.Count; i++)
                  {
                      row.Cells[i].Text = '$' + row.Cells[i].Text;
                      if (row.Cells[i].Text.Contains('-'))
                      {
                          row.Cells[i].Text = row.Cells[i].Text.Replace('-', ' ');
                          row.Cells[i].Text = '(' + row.Cells[i].Text;
                          row.Cells[i].Text += ')';
                          row.Cells[i].ForeColor = Color.Red;
                      }
                  }
              }
              var query2 = dt2.AsEnumerable().GroupBy(c => c.Field<string>("Name")).Select(c => new
              {
                  Name = c.Key,
                  A = c.Sum(d => d.Field<double>("A")),
                  B = c.Sum(d => d.Field<double>("B")),
                  C = c.Sum(d => d.Field<double>("C")),
                  D = c.Sum(d => d.Field<double>("D")),
                  E = c.Sum(d => d.Field<double>("E")),
                  F = c.Sum(d => d.Field<double>("F")),
                  G = c.Sum(d => d.Field<double>("G")),
                  H = c.Sum(d => d.Field<double>("H")),
                  Total = c.Sum(d => d.Field<double>("Total"))
              });
              GridView1.DataSource = query2.ToList();
              GridView1.DataBind();
              foreach (GridViewRow row in GridView1.Rows)
              {
                  for (int i = 0; i < GridView1.Columns.Count; i++)
                  {
                      row.Cells[i].Text = '$' + row.Cells[i].Text;
                      if (row.Cells[i].Text.Contains('-'))
                      {
                          row.Cells[i].Text = row.Cells[i].Text.Replace('-', ' ');
                          row.Cells[i].ForeColor = Color.Red;
                      }
                  }
              }
          }
  

  The output as below:

  

  Best regards,
  Dillion

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.