How to join Join two DataRows into a single DataRow through Column Name?

Question

I am trying to figure out how to join two respective datarows into single datarow in dataset through Department column Name.

In provided dataset output i want to join Gastroenterology and Medical Gastroen(two datrows) through column name to single datarow (similar to Required Final dataset Output with Merged Rows).

Need Your ideas/help how it can be accomplished in asp.net and/or C#.

DataSet Output

     Department    Male Visit    Female Visit    Total Count
     ----------    ----------    ------------    -----------
    Endocrinology     10             20              30
  Gastroenterology    15             25              40
  General Medicine    25             05              30
  Medical Gastroen    30             20              50

Required Final Dataset Output with Merged Rows

     Department    Male Visit    Female Visit    Total Count
     ----------    ----------    ------------    -----------
    Endocrinology     10             20              30
  Gastroenterology    45             45              90
  General Medicine    25             05              30

Hope [How to merge two rows into one row in sql](http://stackoverflow.com/a/15411363/3796048) helps — Mohit S, Dec 07 '15 at 04:43
Why not do that before putting the data in the dataset/database ? — mybirthname, Dec 07 '15 at 05:05
check this http://stackoverflow.com/questions/16945569/join-2-datarow-rows-into-one-row-c-sharp — Francis Saul, Dec 07 '15 at 05:07
@mybirthname I also want to suggest that, but he need to do this via code behind — Khazratbek, Dec 07 '15 at 05:07
@Pratik you need strictly add some rows or if have to be fully dynamic? — Khazratbek, Dec 07 '15 at 05:07

score -1 · Answer 1 · answered Dec 07 '15 at 05:12

I think you must use joining for this in your database query. that would be better. Table A

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Table B.

+-----+---------------------+-------------+--------+
| OID | DATE                |          ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

SQL QUERY SQL> SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Resulted Table:

+----+----------+--------+---------------------+
| ID | NAME     | AMOUNT | DATE                |
+----+----------+--------+---------------------+
|  3 | kaushik  |   3000 | 2009-10-08 00:00:00 |
|  3 | kaushik  |   1500 | 2009-10-08 00:00:00 |
|  2 | Khilan   |   1560 | 2009-11-20 00:00:00 |
|  4 | Chaitali |   2060 | 2008-05-20 00:00:00 |
+----+----------+--------+---------------------+

I hope it will help you.

score -1 · Answer 2 · answered Dec 07 '15 at 05:32

You can Do something like this.

        DataTable _dataTable = new DataTable();
        DataRow _dataRow1 = null;

        _dataTable.TableName = "Products";
        _dataTable.Columns.Add("ID",typeof(int));
        _dataTable.Columns[0].AutoIncrementSeed = 1;
        _dataTable.Columns[0].AutoIncrement = true;
        _dataTable.Columns.Add("ProductsName");
        _dataTable.Columns.Add("Price");

        _dataRow1 = _dataTable.NewRow();
        _dataRow1["ProductsName"] = "Sony Laptop";
        _dataRow1["Price"] = "15000";

        _dataTable.Rows.Add(_dataRow1);

        DataRow _dataRow2 = null;
        _dataRow2 = _dataTable.NewRow();
        _dataTable.Rows.Add(_dataRow2);
        _dataRow2["ProductsName"] = "LG Laptop";
        _dataRow2["Price"] = "15000";

        DataSet _dataSet = new DataSet();
        _dataSet.Tables.Add(_dataTable);

How to join Join two DataRows into a single DataRow through Column Name?

2 Answers2