SQL Server Subquery as a List

I have a subquery that I want to aggregate into a semi-colon separated list. This enables you to have a single query that has a list of items associated with the result set. In this example, I am trying to get the Categories associated with a Course.

In the SQL database, I have a join table called CoursesCategories that is a many-to-many joining table. Therefore, Courses can have multiple Categories associated with a single Course. This is similar to the approach of using group_concat() in MySQL.

I am using the SQL Server specific functionality, including the FOR XML clause and the STUFF function.

The FOR XML clause is usually used to create XML documents from your query result. However, we can also specify an empty root node to be wrapped around our subquery result, which in affect concatenates all of the results into a single string. Then, I use the STUFF function to replace the leading sem-colon with an empty string, effectively removing it.

Here is the sample code:

SELECT crs.CourseID, STUFF(
  SELECT ';' + Categories.CategoryTitle
  FROM Categories
  WHERE CategoryID IN (SELECT CategoryID
    FROM CoursesCategories
    WHERE CourseID = crs.CourseID
  )
  FOR XML PATH ('')
), 1, 1, '')
FROM Courses crs