How to merge row items into one row with each value separated by a comma?


My DataTable looks like this:

In my automation, I want to merge the row values under each column into a single row, with each value being separated by a comma. This is what the DataTable should look like:

Can anyone advise the best way to do this?

For Each Row through the datatable and add each value to a separate array, so you end up with ProdArray {123,456,789} and PriceArray {1.2,4.3,5.3}

Then use Join to concatenate the arrays into a string. Then add those strings as a row in the datatable.


How about the following?

dt = {dt.Clone.LoadDataRow(dt.Columns.Cast(Of DataColumn).Select(Function(c) String.Join(",",dt.AsEnumerable.Select(Function(r) r(c.ColumnName).ToString))).ToArray,False)}.CopyToDataTable


Thank you for your response, I like this solution very much. With this line, how would it be possible to specify the column names? For example if I have four columns, but only want the “Product Number” and “Price” to be merged?


Do you want to create datatable which has 2 columns? If so, the following will work.

targetCols={"Product Number","Price"}
dtResult = {dt.Clone.LoadDataRow(dt.Columns.Cast(Of DataColumn).Select(Function(c) String.Join(",",dt.AsEnumerable.Select(Function(r) r(c.ColumnName).ToString))).ToArray,False)}.CopyToDataTable.DefaultView.ToTable(False,targetCols)


That works great, thank you. What if I want to keep all four columns, but only have the target columns merged?


In this case, you need to clarify what value should be in remaining columns.

If it’s first rows value in remaining columns, for example, the following will work.

dtResult = {dt.Clone.LoadDataRow(dt.Columns.Cast(Of DataColumn).Select(Function(c) if(targetCols.Contains(c.ColumnName),String.Join(",",dt.AsEnumerable.Select(Function(r) r(c.ColumnName).ToString)),dt.Rows(0).Item(c.ColumnName))).ToArray,False)}.CopyToDataTable


Great, thanks Yoichi!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.