How can I group data with same value in multiple rows, in two columns?

Rohin

I have some data in a SQL server table with different To and CC recipients listed in two columns.

Is it possible to get different data sets with all rows that have the same values in To and CC columns?

Would it be easier to split the datatable in my C# code?

Suppose I have data like this :

  A       B       C       To            CC
-----   -----   -----    -----         -----
  132    45     test     em1@cc        ec7@cc
  133    34     new      em4@cc        ec3@cc
  134    65     here     em3@cc        ec5@cc
  135    78     one      em7@cc        ec4@cc
  136    45     check    em1@cc        ec5@cc
  137    85     two      em6@cc        ec1@cc
  138    56     email    em1@cc        ec5@cc
  139    65     var      em3@cc        ec5@cc

I would like to get the data separated as below (Rows where To value is same, and CC value is same):

  A       B       C       To            CC
-----   -----   -----    -----         -----
  132    45     test     em1@cc        ec7@cc


  A       B       C       To            CC
-----   -----   -----    -----         -----
  133    34     new      em4@cc        ec3@cc

  A       B       C       To            CC
-----   -----   -----    -----         -----
  134    65     here     em3@cc        ec5@cc
  139    65     var      em3@cc        ec5@cc

  A       B       C       To            CC
-----   -----   -----    -----         -----
  135    78     one      em7@cc        ec4@cc

  A       B       C       To            CC
-----   -----   -----    -----         -----
  136    45     check    em1@cc        ec5@cc
  138    56     email    em1@cc        ec5@cc


  A       B       C       To            CC
-----   -----   -----    -----         -----
  137    85     two      em6@cc        ec1@cc

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Gajendra Jangid4y ago
Try this link

https://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns
Sachin Singh4y ago
1. protected void Page_Load(object sender, EventArgs e)
2. {
3. if (!this.IsPostBack)
4. {
5. DataTable dt = new DataTable();
6. GridView1.DataSource = GetData("SELECT * FROM mailTable GROUP BY CC, To");
7. GridView1.DataBind();
8. }
9. }
10. private DataTable GetData(string query)
11. {
12. DataTable dt = new DataTable();
13. string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
14. using (SqlConnection con = new SqlConnection(constr))
15. {
16. using (SqlCommand cmd = new SqlCommand(query))
17. {
18. using (SqlDataAdapter sda = new SqlDataAdapter())
19. {
20. cmd.CommandType = CommandType.Text;
21. cmd.Connection = con;
22. sda.SelectCommand = cmd;
23. sda.Fill(dt);
24. }
25. }
26. return dt;
27. }
28. }
1. protected void OnDataBound(object sender, EventArgs e)
2. {
3. for (int i = GridView1.Rows.Count - 1; i > 0; i--)
4. {
5. GridViewRow row = GridView1.Rows[i];
6. GridViewRow previousRow = GridView1.Rows[i - 1];
7. for (int j = 0; j < row.Cells.Count; j++)
8. {
9. if (row.Cells[j].Text == previousRow.Cells[j].Text)
10. {
11. if (previousRow.Cells[j].RowSpan == 0)
12. {
13. if (row.Cells[j].RowSpan == 0)
14. {
15. previousRow.Cells[j].RowSpan += 2;
16. }
17. else
18. {
19. previousRow.Cells[j].RowSpan = row.Cells[j].RowSpan + 1;
20. }
21. row.Cells[j].Visible = false;
22. }
23. }
24. }
25. }
26. }