Getting records from multiple rows from a table into one field

This is a very neat little trick in TSQL.

Say you've got a table called STATES, which contains 2 columns: STATE_ABBREVIATION and STATE_DESC.

If you want to get all the state abbreviations beginning with M into a comma separated field (say for a return value from a stored procedure for a report), check out this technique:

declare @result varchar(max)

set @result = ''

select @result = @result + STATE_ABBREVIATION + ','
from STATES
where STATE_ABBREVIATION like 'M%'

select LEFT(@result,LEN(@result)-1)

Here's the output:

MA,MD,ME,MI,MN,MO,MS,MT

The statement in bold is the one that does the work. It's like a recursive loop without having to code it.