none
Data Table with similar Column as one single row RRS feed

  • Question

  • Hi Guys,

    We have an datatable with below table structure

    SlNo                    PON1              PON2              PON1212121                   FirstName                      LastName

    1                           2                    3                       4                                  XYZ                                XYZ

    We would like to merge all PON as one column as Below

    SlNo                    PON              FirstName                      LastName

    1                           2,3,4                   XYZ                                XYZ

    Any suggestions would be appreciated.

    Regards,

    Navin


    Navin.D http://dnavin.wordpress.com

    Wednesday, March 7, 2018 12:55 AM

All replies

  • From your original DataTable get all the columns you need and concatenate the columns that you wanted to merge, and transfer them to a new DataTable with your new structure of concatenated column.

    Old DataTable

    SlNo                    MERGE(PON1,PON2,PON1212121)                   FirstName                      LastName

    New DataTable

    SlNo                    MERGE              FirstName                      LastName

    Wednesday, March 7, 2018 1:01 AM
  • How many DataRows are you dealing with?

    What if for FirstName we had (row wise)

    ABC

    DDD

    ABC

    XAD

    ABC

    How do you expect to see ABC as a single ABC or ABC,ABC,ABC ???

    Have you included "all rules" as anyone looking to assist you need not provide a proposed solution and then you come up with another rule (this happens here frequently so I need to ask)

    What version of the Framework are you using e.g. 2.0, 3.5, 4.6 etc ?

    Are you comfortable with LINQ/Lambda ?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, March 7, 2018 1:02 AM
    Moderator
  • From your original DataTable get all the columns you need and concatenate the columns that you wanted to merge, and transfer them to a new DataTable with your new structure of concatenated column.

    Old DataTable

    SlNo                    MERGE(PON1,PON2,PON1212121)                   FirstName                      LastName

    New DataTable

    SlNo                    MERGE              FirstName                      LastName

    You might provide Navind a very simple code sample for the following. If I were a new developer and saw this response I would ask you to provide a code sample for the following.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, March 7, 2018 1:06 AM
    Moderator
  • From your original DataTable get all the columns you need and concatenate the columns that you wanted to merge, and transfer them to a new DataTable with your new structure of concatenated column.

    Old DataTable

    SlNo                    MERGE(PON1,PON2,PON1212121)                   FirstName                      LastName

    New DataTable

    SlNo                    MERGE              FirstName                      LastName

    Please disregard this idea, because if you have million rows of data this would not be efficient.

    Instead try this idea.

    Column1 Column2 Column3 NewColumn(With merge data from Column2 & Column3)

    Loop through all your rows and concatenate the columns you want to merge and transfer them to your new column. Then later on you have the option to delete your old columns that are merged to the new merged column.

    DataTable table1 = new DataTable();
    table1.Columns.Add("StudentNo"typeof(string));
    table1.Columns.Add("StudentFirstName"typeof(string));
    table1.Columns.Add("StudentLastName"typeof(string));
     
    table1.Rows.Add("ABC1""John""Doe");
    table1.Columns.Add("StudentFullName"typeof(string));
     
    foreach (DataRow row in table1.Rows)
    {
        row["StudentFullName"= $"{row["StudentFirstName"]}{row["StudentLastName"]}";
    }

    But if you have access to the data before transferring it to the DataTable I suggest merge it to the Database layer that would be the best option.

    @Kareninstructor I will try my best to provide it thank you

    Wednesday, March 7, 2018 1:33 AM
  • Now that is an excellent reply :-)

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, March 7, 2018 1:38 AM
    Moderator
  • Hi Guys,

    The column name Pon can be 3 times to n times with same table name; may be i need to loop through identify all the column name starts with  PON and merge them;

    I Know contain method can be used to identify the column; but how does it look for all the column which start with Pon?

     if (Regex.IsMatch(Column.ToString(), "PON"))
                                    {
                                        Column.ToString();

                                    }

    Add them to List and used it with merge for that column. 

    Karen you are right we have multiple columns with that issue; we are planning to write an generic method to us  it.

    Regards,

    Navin


    Navin.D http://dnavin.wordpress.com



    • Edited by Navind Wednesday, March 7, 2018 6:05 PM
    Wednesday, March 7, 2018 5:42 PM
  • I can't get to Visual Studio right now, at a session at microsoft so the following was done in notepad. You can get columns via code like this.

    var cols = dt.Columns.Where(col => col.ColumnName.StartsWith("PON")StringComparison.InvariantCultureIgnoreCase.ToList()
    I believe you should be able to collect row data via a lamda statement


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, March 7, 2018 5:59 PM
    Moderator
  • Hello please try this code, I got the idea from karen

    This will dynamically merge all the columns into one column that starts with a string "PON"

                DataTable table1 = new DataTable();
                table1.Columns.Add("StudentNo", typeof(string));
                table1.Columns.Add("PONStudentFirstName", typeof(string));
                table1.Columns.Add("PONStudentLastName", typeof(string));
    
                table1.Rows.Add("ABC1", "John", "Doe");
                table1.Columns.Add("StudentFullName", typeof(string));
    
                var ponCol = table1.Columns.Cast<DataColumn>().Where(c => c.ColumnName.StartsWith("PON"));
    
                foreach (DataRow row in table1.Rows)
                {
                    string colValue = string.Empty;
                    foreach (DataColumn col in ponCol)
                    {
                        colValue += $"{row[col].ToString()},";
                    }
                    row["StudentFullName"] = colValue;
                }




    • Edited by The Code Breaker Thursday, March 8, 2018 2:13 AM
    • Marked as answer by Navind Friday, March 9, 2018 7:15 PM
    • Unmarked as answer by Navind Friday, March 9, 2018 7:22 PM
    Thursday, March 8, 2018 2:08 AM