Sum() For Textual Fields?

Hi all,

Many times would be useful (for me at least) if sum() could summarize
textual fields by simply concatenating them :

eg a table named 'lessons' contains
Lesson Teacher
math Mr. Brown
history Mr. Brown
math Ms. White
gym Mr. Green
geo Ms. White

so I could use a select like this:

select Lesson,sum(Teacher) group by Lesson

(and sum might take a second parameter as a separator between added fields)

Of course, these things aren't exists so I wanted to write my own
"textsum()" function, but in MSSQL2000 user functions can take only
scalar variables AFAIK.

Anyone has faced and/or has solved this problem other way (with a simple
select)?

Thanks

zf