I have a table containing client visit records that looks like the following (I've simplified it for illustration)
ClientID_FK VisitDate VisitNotes
1 29/05/08 'Note 1.'
2 28/05/08 'Called by Phone.'
3 03/05/07 'Will call back.'
3 14/09/08 'Note 2.'
2 13/08/08 'Refer to notes.'
2 04/04/08 'blah blah blah.'
1 11/11/06 'More notes...'
There is also a clients table which is unique on the client number, it has a VarChar(MAX) comments field which I'd like to be a concatenation of the visit notes in reverse chronological order... it needs to end up looking something like this..
1 'Note 1. More notes...'
2 'Refer to notes. Called by Phone. blah blah blah.'
3 'Note 2. Will call back.'
I've tried grouping the visit table by client number but don't know what aggregate function to use to get what I need...assuming that's the best approach.
Any ideas folks?