Tip: Concatenate rows with XML, or COALESCE

Concatenate rows with XML

I still reference my Forgotten T-SQL Cheat Sheet, especially for the XML trick that turns a set of rows into a list. The T-SQL Cheat Sheet example uses a parent-child table combination, but what if we want to get the same effect from a single table? Here:

SELECT	DISTINCT Person ,
		Books = STUFF(( SELECT	',' + Book
				FROM	Books AS B2
				WHERE B2.Person = B1.Person	
				ORDER BY Book
				FOR XML	PATH('')
				), 1, 1, '')
FROM	Books AS B1;

This works for cases where you need several rows of comma delimited lists. So, data like this:

Could be easily run through the above query,to get a result like this:

Concatenate rows with COALESCE

Now, a simpler case to concatenate rows is to just use COALESCE. It won’t give you multiple rows, but sometimes you just need a single, simple list:

    DECLARE	@Books VARCHAR(8000);

    SELECT @Books = COALESCE(@Books + ', ', '') + Book FROM Books;

    SELECT @Books as AllBooks;

If we used this on the same data set, we’d get this:

Just a quick reminder from your friendly neighborhood SQL-woman.
Happy days,
Jen
http://www.MidnightDBA.com/Jen