Query to put multiple values into one column

[mdProgrammer]

I'm using SQL Server 2000, and I have a query that gets names and locations, sort of like this -

Name		Location
Mary		Atlanta
Sue		Chicago
Sue		Seattle		
Jim		Boston
Jim		Chicago
Jim		Miami

I want to get the locations on a single line like below. I know SQL Server 2008 has wm_concat(), but the application that this query is being written for still uses SQL Server 2000 (we're in the process of upgrading our servers). I did something like this before, but I think it was with datagrids, but I can't remember exactly what the code was. Is there a simple way besides using cursors?

Name		Location
Mary		Atlanta
Sue		Chicago, Seattle
Jim		Boston, Chicago, Miami

 

[gmmastros]

Take a look here: thread183-1159740


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mdProgrammer]

It works, thanks.

 

[markros]

SQL Server 2008 does not have wm_concat(), where did you hear about it?

PluralSight Learning Library