Appending different values from same column with each other

Question

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.

Answer 1

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]