Date: 09/27/08 (SQL Server)    Keywords: no keywords

Help me with this problem, please. There are three parts.

I have a table with milestones and dates:
FRID    ResourceKey    Milestone
1005    1    9/15/2008
1005    4    11/13/2008
1005    5    8/18/2008
1005    88    11/30/2008
1008    1    9/17/2008
1009    1    9/16/2008
1009    2    11/13/2008
1009    4    11/1/2008
1009    5    8/18/2008
1009    88    11/30/2008
1010    1    9/17/2008
1010    4    10/22/2008
1010    88    11/10/2008
1014    1    9/15/2008
1014    4    10/14/2008
1014    5    8/27/2008

And a table for key-text.
ResourceKey    FormDescription    SortOrder
1    CC Contacted    8
2    CC Meeting Held    9
3    CEO Meeting Held    10
4    Campaign Start    11
5    AI Completed    2
6    Match Agreement Signed    3
10    Recommendations Complete    14
12    Thank You Sent    16
13    Received Management Support    1

What I want to do is assemble a single text note for each company like this:
FRID    Note
1005    08/21/2007--AI Completed;09/27/2007--CC Contacted;10/18/2007--CC Meeting Held;...
1014    09/06/2007--AI Completed;10/08/2007--CC Contacted;11/01/2007--CC Meeting Held;...
...


First part: How can I get those notes, the way they're supposed to be formatted, in one statement, without using a cursor and a temp table?
(That's how I did it for 2007... I'd like to do it The Right Way for 2008)


The notes table has a three-part primary key and a "note" text field.
Frid, notedate, notesequence

When I insert the data, each company will have a different sequence for the note (depending on how many other notes have been inserted today. I need to have it be one more than the current max for each one.

Second part: How can I insert the rows into the notes table all at once, without looping through?


A problem is that companies might have records merged during the year, for some reason. So, while in the milestones table, there's 3 separate FRIDs, there's only one frid in the notes table. Of course there's a table listing the merged frid and the new frid.

Third part: How can I insert notes all at once, when the data might have more than one row for a particular FRID?

Source: http://community.livejournal.com/sqlserver/68063.html