Loop through subquery records and create variable to store in field

[glgcag]

New to SQL Server and I'm having a BLAST! Converting an Access database, and really enjoying the experience. Since I'm not the most experienced SQL guy yet, I was wondering what the best approach might be to this problem:

I have a simple query with four tables: tAGProjects, tProjectTypes, tProjectAgents and tAgents.

SELECT tAGProjects.AGProjectKey, tAGProjects.ProjectTitle, tAGProjects.ClosedProject,
tAGProjects.Created, tProjectTypes.ProjectTypeDesc, tAgents.AgentName
FROM tAGProjects
LEFT JOIN tProjectAgents ON tAGProjects.AGProjectKey = tProjectAgents.AGProjectKey
LEFT JOIN tAgents ON tProjectAgents.AgentKey = tAgents.AgentKey
LEFT JOIN tProjectTypes ON tAGProjects.ProjectTypeKey = tProjectTypes.ProjectTypeKey

Often there are two or more agents on a project, and rather than returning multiple rows for a project when there are multiple agents, I'd like to gather all the agents into a comma separated variable and insert it into my returned recordset. Here's an example of the multiple records:

Project Type Created Agents
TestP Type1 1/1/2007 CT
TestP Type1 1/1/2007 MM

Here's an example of what I'd like to do:

Project Type Created Agents
TestP Type1 1/1/2007 CT,MM

My initial thoughts are to create a temp table, then populate it with the distinct records, then cycle through the distinct records (using a cursor?) and run a query that creates the comma separated variable and inserts it into the temp table.

Not sure this is the best way to go, since I've been told over and over again to avoid cursors at all costs. I just can't think of a better way to do it. Any suggestions?

 

[LNBruno]

Check out these two FAQs:

http://www.tek-tips.com/faqs.cfm?fid=6466

http://www.tek-tips.com/faqs.cfm?fid=6485

And then read some of what you find here:
T-SQL Cursors

< M!ke >
[small]"Oops" is never a good thing.[/small]

 

[glgcag]

LNBruno,

Thanks for the references! I will look at these in detail- so far it seems that my needs are pretty simple and a cursor won't do much harm, but I'll experiment a bit with the other way of doing it and see what I get!