MSSQL subquery as a list.. i.e. return categories as a select item

If you have ever wondered how to return a list of categories with a select statement heres the answer. It actually comes from StackOverflow.This is a repost so I can find it later as I am sure that I have needed to do this about 20 times in the past.

This is Really clever and full credit goes to this really Smart guy Niikola


Here is a simple-ish query, gets some user data along with the list of categories. The Category table is separate with a linking table called contact_category_xref which holds the category id and the contact id associated.

SELECT
c.id,
c.FirstName,
c.LastName,
c.email,
c.City,
c.State,

Stuff((select ','+cat.name from category cat left join 
        contact_category_xref x
        on cat.id = x.catid
        where x.contactid = c.id
        For XML PATH('')),1,1,'') as categoryList
FROM contacts c 

Ill attempt some explanation, feel free to jump in and offer corrections please.

The first bit of Magic comes in the form of the "For XML Path('')" command in sql returns the query columns as XML which is incredibly handy.

  "pizza,pie"

Now to get rid of the xml portion concatenating the comma before the cat.name returns the list without XML. example ',pizza,pie,cookies,snickers'
I have no Idea how this works or why but it does. Please feel free to explain if you know.

So Whats left is the STUFF command which removes the first character for 1 character in length and replaces it with ''. Which means we are left with our nicely formatted list 'pizza,pie,cookies,snickers'.

Pretty COOL i must say.