Merging rows in a data table based off a shared value

[Garang]

Hello, I am currently working with some data tables from CSV data but I need to work over the data a bit before aggregating it. What I want to do is merge or group the rows of a single data table based off a shared value in one of the rows.

Stats	UserID	Last Name	First Name	Location
Stat1	ID3	User1	Test	Location1
Stat2	ID3	User1	Test	Location1
Stat3	ID3	User1	Test	Location1
Stat1	ID4	User2	Test2	Location1
Stat2	ID4	User2	Test2	Location1
Stat1	ID5	User3	Test3	Location1
Stat2	ID5	User3	Test3	Location1
Stat3	ID5	User3	Test3	Location1
Stat4	ID5	User3	Test3	Location1
Stat5	ID5	User3	Test3	Location1
Stat6	ID5	User3	Test3	Location1

So in this example, I would want the first column to combine Stats1-3 using Column 3 (the UserID) as the shared value. So the first column would be “Stat1, Stat2, Stat3” for User ID3, “Stat1, Stat2” for ID4 and so on, with the other columns remaining the same. What would be the cleanest and quickest way to run through the data table and do this?

[Yoichi]

Hi,

Can you try the following sample?

dtNew = dt.AsEnumerable().GroupBy(Function(r) r("UserID").ToString).Select(Function(g) dt.Clone.LoadDataRow({String.Join(",",g.Select(Function(r2) r2("Stats").ToString()).Distinct()),g.Key,String.Join(",",g.Select(Function(r2) r2("Last Name").ToString()).Distinct()),String.Join(",",g.Select(Function(r2) r2("First Name").ToString()).Distinct()),String.Join(",",g.Select(Function(r2) r2("Location").ToString()).Distinct())},False)).CopyToDataTable()

Result

Sample
Sample20250912-1.zip (10.4 KB)

Regards,

[sudster]

@Garang

CSV data but I need to work over the data a bit before aggregating it

When you say “aggregating”, are you wanting to run an aggregation function (count, sum, avg etc) over stats by userid?

[Garang]

Thanks, this works perfectly.