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.
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