Multiple rows with all the same data except one field

[catchingup]

I am writing a stored procedure in SQL2000.

The result set returns a set of information that was a single row per instance exceptin the SUPPORTS column.
Some individuals have multiple entries so it creates multiple rows (a sampling of the data is below):

ID NAME SUPPORTS HIREDATE
OFTC5820 Wilson Karens 12-30-06
OFTX7783 Watkins Jarvis 11-15-05
OFTX7783 Watkins Spellman 11-15-05
OFTX7783 Watkins Kneeland 11-15-05
OFHT2379 Washdon Jackson 05-01-04
OFHT2379 Washdon Michels 05-01-04

I need to modify my query so that the multiple line entries are deleted and the SUPPORTS entries are combined into a single entry (like this):

ID NAME SUPPORTS HIREDATE
OFTC5820 Wilson Karens 12-30-06
OFTX7783 Watkins Jarvis, Spellman, Kneeland 11-15-05
OFHT2379 Washdon Jackson, Michels 05-01-04

I believe this can be done but am not certain how to go about it.
Any suggestions are appreciated.

 

[SQLBill]

Are you just wanting to return the data that way or are you going to update the database to store it that way? Storing it that way (concatenated into one value) is a bad idea, it makes 'matching' and retrieving data much more difficult.

To display the values the way you want, take a look at this FAQ183-6466.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[ESquared]

I hope that the individual tables are normalized and you don't have, say, hiredate for the same person in two rows...