Concatenate multiple rows for a column using LINQ

Note: I wrote this post after I encountered a programming problem.

Lets say I have 4 columns in DB2 table called “Table1”-

Id (int)

Code (varchar)

SequenceNumber (int)

Message (varchar)

Id, Code and SequenceNumber act as a unique key for this table.

Data is stored in this table using following condition:

For each Id and Code, if message size exceeds column MAX size,

then the message is split and stored in multiple rows using SequenceNumber in incremental form.


Consider this table has following data:

Id Code SequenceNumber Message
1 ABC 1 test1
1 ABC 2 test2
2 XYZ 1 message1
2 XYZ 2 message2
3 PQR 1 hello

The requirement is to concatenate messages for all sequence numbers in Message column, for each row which has unique combination of {Id and Code}.

So my required table should have following columns-



Message (concatenated messages)

Id Code Message
1 ABC test1 test2
2 XYZ message1 message2
3 PQR hello

Please note that SQL CONCAT function concatenates two strings or two column values. Here we want to concatenate column values across rows.

I am not sure if this requirement can be programmed using SQL query. We had this exact requirement in our project and I used LINQ to implement it.

Here are the steps I used in VB.NET (it being the programming language used in our project)-

1) Connect to DB2

2) Fetch all data from Table1 and store it in DataTable called “dt”. This DataTable has 4 columns – Id, Code, SequenceNumber and Message.

3) Write LINQ query to concatenate data as per the requirement. Here is the VB.NET code

Dim query = From r In dt _

Order By r.Field(Of Short)(“SequenceNumber”) Ascending _

Group r By ID = r.Field(Of String)(“Id”), _

Code = r.Field(Of String)(“Code”) _

Into g = Group _

Select New With { _

Key ID, Code, _

.MESSAGE = String.Join(“”, g.Select(Function(a) a.Field(Of String)(“Message”)).ToArray())}

Note that grouping is done on columns “Id” and “Code” and rows are ordered in ascending order for column “SequenceNumber”.

I used String.Join to concatenate the values in Message column.

4) “query” object is an IEnumerable collection. I can iterate over it using “for each” loop and extract the data or perform further processing.

I would be delighted to see a better/improved approach to implement this requirement.