Dave + SQL Server = Ingenious SQL….

In a previous post we looked at how we can build out a delimited list using the ISNULL and COALESCE functions. This functionality is great if you need to return the delimited list through a function or persist into a variable, however what if you need to return a comma separated list in a result set? This blog post will look at, and describe, how to build out a delimited or comma separated list using FOR XML PATH clause. Using tricks with the FOR XML PATH clause, a correlated subqueries, and un-named columns will give us all the tools needed to return a delimited list within a result set.

So the idea for this blog post is that we take a list of student’s details and include a comma separated list of the course codes that they have registered for. Here is a data diagram that represents our sample dataset.

Here are the populated tables.

310ICS310Database Management

What we would expect the script to return is a result set with student details including a comma separated list of courses that looks like this:

Using the following temp table structure and sampling of student and course data gives us the data that we will need for this example.

DROP TABLE IF EXISTS #Students
GO
DROP TABLE IF EXISTS #Courses
GO
DROP TABLE IF EXISTS #StudentCourse
GO
 
CREATE TABLE #Students (
   StudentID INT PRIMARY KEY NOT NULL
 , FirstName NVARCHAR(50) NOT NULL
 , LastName NVARCHAR(50) NOT NULL
 )
GO
 
CREATE TABLE #Courses (
    CourseID INT PRIMARY KEY NOT NULL
  , CourseCode NVARCHAR(50) NOT NULL
  , CourseName NVARCHAR(50) NOT NULL
)
 
CREATE TABLE #StudentCourse(
    StudentID INT NOT NULL
  , CourseID INT NOT NULL
)
 
INSERT   INTO #Students (StudentID, FirstName, LastName)
VALUES   (10001, N'Bob',N'Roberts')
       , (10002, N'John',N'Johnson')
       , (10003, N'Tom',N'Tompson')
       , (10004, N'Rich',N'Richardson')
       , (10005, N'Will',N'Williams');
GO
 
INSERT INTO #Courses (
    CourseID
  , CourseCode
  , CourseName
)
VALUES (140, N'ICS140', N'Introduction to Programming')
     , (225, N'ICS225', N'Web Programming')
     , (240, N'ICS240', N'Advanced Programming')
     , (310, N'ICS310', N'Database Management');
 
INSERT INTO #StudentCourse (StudentID, CourseID)
VALUES (10001, 140)
     , (10001, 225)
     , (10001, 240)
     , (10001, 310)
     , (10002, 240)
     , (10002, 310)
     , (10004, 140)
     , (10004, 225)
     , (10005, 140);

We are going to start out by creating a simple query that returns all of the data we need, to generate the output. This will include all data from the student table, all data from the student course weak entity table, and only course code data from the course table.

SELECT s.StudentID, s.FirstName, s.LastName, c.CourseCode
FROM #Students s
LEFT JOIN #StudentCourse sc ON sc.StudentID = s.StudentID
LEFT JOIN #Courses c ON c.CourseID = sc.CourseID

Now that we have our dataset, let’s turn our focus on creating the comma separated list of course codes. Here we will implement our firsts bit of trickery. We are going to use the FOR XML clause, however we are going to pass an empty string to the PATH function. This will rename the XML root node as an empty string, thus not returning a root node. You can test this by running both queries, the first query will have a root node of <row> whereas the second query only returns the <CourseCode> nodes.

SELECT c.CourseCode
FROM #Students s
LEFT JOIN #StudentCourse sc ON sc.StudentID = s.StudentID
LEFT JOIN #Courses c ON c.CourseID = sc.CourseID
FOR XML PATH;
 
SELECT c.CourseCode
FROM #Students s
LEFT JOIN #StudentCourse sc ON sc.StudentID = s.StudentID
LEFT JOIN #Courses c ON c.CourseID = sc.CourseID
FOR XML PATH('');

Being that we were able to remove the root node by simply renaming the node, we need to do the same for the column name CourseCode. Here comes some more trickery, if we concatenate a string and do not provide an alias, the column name is undefined and thus is empty. In Management Studio you should see something like this “(No column name)”. This trickery actually works out to have an advantage because we need to concatenate our strings with a comma to create a comma separated list of values. So running the following query will give us all of the values in a comma separated list.

SELECT ', ' + c.CourseCode
FROM #Students s
LEFT JOIN #StudentCourse sc ON sc.StudentID = s.StudentID
LEFT JOIN #Courses c ON c.CourseID = sc.CourseID
FOR XML PATH('');

So now we have all of the course codes in one big comma separated list, we need to get them into sub lists for each student. If we nest the query with the FOR XML clause in a sub query and reference the student id from the outer query, this will limit the comma separated lists to only the course codes that are linked to the students.

SELECT s.StudentID
	 , s.FirstName
	 , s.LastName
	 , Courses = (
			SELECT ', ' + c.CourseCode
			FROM #StudentCourse sc JOIN #Courses c ON c.CourseID = sc.CourseID
			WHERE s.StudentID = sc.StudentID
			FOR XML PATH(''))
FROM #Students s;

The last piece is a bit of data clean up, using a STUFF or SUBSTRING we can remove the extra comma and white space from the comma separated list. Here is the final query that will return the result with a comma separated list in the result set.

SELECT s.StudentID
	 , s.FirstName
	 , s.LastName
	 , Courses = SUBSTRING((
			SELECT ', ' + c.CourseCode
			FROM #StudentCourse sc JOIN #Courses c ON c.CourseID = sc.CourseID
			WHERE s.StudentID = sc.StudentID
			FOR XML PATH('')),3,1000)
FROM #Students s;