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

Help Activities
, , ,
1

Hello,

My DataTable looks like this:
image

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:
image

Can anyone advise the best way to do this?

2

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.

3

Hi,

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

Regards,

4

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?

5

Hi,

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)

Regards,

6

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

7

Hi,

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

Regards,

8

Great, thanks Yoichi!

1 Like
9

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