Help needed with simple many-to-one result set query 1

[desperateUser]

I'm not able to adequately search for previous posts with this because I'm not sure how to wordsmith the question. This will totally seem like a n00b ? too. TIA...

I have a simple table with an index field, two fields that when combined make a reference number and a numeric field that represents a person. That's it.

INDEX prefix ref_num person
36087 A 123 36897
36088 B 456 12348
36089 A 897 55868
36090 A 897 36981
36091 A 897 48963

How do I build a query that will return this:

prefix ref_num person(s)
A 123 36897
B 456 12348
A 897 55868, 36981, 48963

The number of persons involved with each reference number can range from one to 15+

Like I said, thanks in advance for help with this. I can't seem to wrap my brain around this one.

Penelope in Portlnad

 

[markros]

At the top of this article was a function that can be used for SQL 2000- that's why I provided that link.

In any case, looks like your problem is in the database compatibility level as George found out.

PluralSight Learning Library