I would like to return a concatenated string from a select statement rather than a series of records.

I realise I can just return a table and then convert it into to string myself, but I wondered if there was a SQL Server way of doing this, that return the string as some sort of scalar value.

at present I would have to do the following

Select myfield from mytable

mystring=''
for i = 1 to reccount
mstring = myfield + chr(13)
move to next record
next i


I am trying to remove the code loop above

Many Thanks