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
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
WHERE CategoryID IN (SELECT CategoryID
WHERE CourseID = crs.CourseID
FOR XML PATH ('')
), 1, 1, '')
FROM Courses crs