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-

Id

Code

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.

Advertisements

One thought on “Concatenate multiple rows for a column using LINQ

  1. Hi there, just became aware of your blog through Google, and found that it is really informative. Im going to watch out for brussels. Ill be grateful if you continue this in future. Lots of people will be benefited from your writing. Cheers! cdfgbkebgdaf

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s