Tip: Concatenate rows with XML, or COALESCE

Edit: I went and changed the first query without paying close enough attention. I’ve fixed the explanation and the query.

Concatenate rows with XMLMoonAndHEROnly

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:

PERSON BOOK
Bob Book1
Bob Book2
Bob Book3
Tom Book7
Tom Book9

 

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

PERSON BOOK
Bob Book1,Book2,Book3
Tom Book7,Book9

 

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:

AllBooks
Book1,Book2,Book3,Book7,Book9

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

4 thoughts on “Tip: Concatenate rows with XML, or COALESCE

  1. Pingback: С миру по нитке, №10 | Things I think about

  2. David O'Brien

    Most elegant solution out there, and the simplest to implement. This one saved me a ton of time; thanks much!

Comments are closed.