Using SQL to concatenate values from multiple rows into a single string

Lots of SQL programmers probably know this trick already, but it was a new one for me and seems worth sharing. In just a few lines of code, without using a cursor, you can concatenate values from multiple rows into a single string. For example, if you have a table with a FirstName column, and a SELECT statement returns the FirstName values 'Aaron', 'Betty' and 'Carol', it's easy to form a string like this: 'Aaron, Betty, Carol'. The comments in the code snippet below explain how.

-- create a test table and insert some rows of test data
CREATE TABLE Test (
FirstName VARCHAR(10),
LastName VARCHAR(10)
)
INSERT INTO Test VALUES ('Aaron', 'Aardvark')
INSERT INTO Test VALUES ('Betty', 'Baboon')
INSERT INTO Test VALUES ('Carol', 'Condor')

-- form a comma-delimited list of the FirstName values from all records
DECLARE @myList VARCHAR(100) -- this works with a VARCHAR or NVARCHAR variable, but _not_ with a CHAR variable
SET @myList = '' -- initialize the variable; if you don't, the output string will be blank

-- The next SELECT statement is the key. It appends the FirstName from each record to whatever is in the string so far.
-- The CASE statement is just for putting commas between the names, but not in front of the first one.
SELECT @myList = @myList + CASE @myList WHEN '' THEN '' ELSE ', ' END + FirstName FROM Test
SELECT @myList AS MyList -- output the string, which should say 'Aaron, Betty, Carol'

-- delete the test table
DROP TABLE Test