Appending different values from same column with each other

3
votes

I have written one windows service that sends an email depending upon entries in the table. This service picks pending emails from table sends it to specified email address.

Attributes of my table are Sender, Receiver, Subject, Body.

I am able to retrieve all the entries from table by writing procedure. But most of the times in this table there are entries with same subject, sender and receiver but different body.

So I just want to append Body of emails with same subject.So rather than sending more than one email, body of all such email will be appended and I could send single email only if Subject matches.

Or else what if i am doing this from my windows service C# code?

Please help me out.

1
c#sql
you will have to post some code before anyone can help you. – Thunder Rabbit
Hope this helps you stackoverflow.com/questions/194852/… – V4Vendetta
You might want to put some example code in of how it is retrieveing the values at the moment to make it easier to tell how to group them properly... One thing that might be useful is if you have your data in the right form you can probably use the LINQ group by to group by subject, email, etc. and then aggregate the body... msdn.microsoft.com/en-us/library/… for the group by and that should help see roughly how it works. You'll need to show us some code to be able to create a useful example of use or better pointers for you though. – Chris

1 Answers

1
votes

Thank you for your response. I was able to do it with following query : 

SELECT [Subject], STUFF((SELECT ', ' + [Body] FROM CCSEmails T2 WHERE T1.[Subject] = T2.[Subject] Order By [Body] FOR XML PATH('')),1,1,'') AS [Body] FROM CCSEmails T1 GROUP BY [Subject]