Brian Love
Angular + TypeScript Developer in Denver, CO

SQL Server Subquery as a List

Reading time ~1 minute

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

Brian Love

Hi, I'm Brian. I am interested in TypeScript, Angular and Node.js. I'm married to my best friend Bonnie, I live in Denver and I ski (a lot).