Returning a Comma-Delimited List of Related Records

Returning a Comma-Delimited List of Related Records
By Scott Mitchell

Introduction
In any non-trivial database design, there are related entities within the system. Some entities may share a one-to-many relationship, others a many-to-many relationship. For example, a student database may have a Faculty table that lists its teachers, along with a Students table that contains a row for each student. The Students table may have an AdvisorID, which serves as a foreign-key back to the Faculty table, indicating the student's academic advisor. In such a case there's a one-to-many relationship between faculty and students - a faculty member may have an arbitrary number of students that she's advising. Additionally, there may be many-to-many relationships, such as a Classes table that enumerates the classes offerred, along with an Enrollments table that maps students to classes. The Enrollments table provides a many-to-many relationship between students and classes, since each student may be enrolled in multiple classes and each class may have multiple students enrolled.

In either type of relationship, there are times when we may need to provide a comma-delimited list of related records. For example, the dean may want to see a report that lists each faculty member followed by a comma-delimited list of students she is advising; a professor may want a print out of the list of classes he's teaching, followed by a comma-delimited list of students taking the course that semester; an accountant for the school might want to see a list of students, showing a comma-delimited list of each class that student is currently taking. For example, the dean's report may have the following output:

Faculty Students Advising

Scott John, Paul, George, Ringo

Jisun Tito, LaToya, Michael, Janet

Sam Carl, Johann, Christoph

Dave Not advising any students

To provide such functionality there's basically two approaches that can be taken:

Retrieve the data using the typical JOIN notation, returning a row for each pair of matching results. Then, in the 'client' (i.e., your server-side web application), combine the returned data into a comma-delimited list, or
Formulate the comma-delimited list in SQL

In this article we'll briefly examine the first approach and then delve more deeply into the second. Read on to learn more!

- continued -

Creating the Comma-Delimited List in the Client Application
When building data-driven web applications that require displaying a report with a comma-delimited list of related records, I usually craft the output in the web application itself, rather than building it via SQL syntax. That is, I use the standard SQL JOIN constructs to return a record for each pair of related data and then loop through the returned results, building up the output as needed.

To illustrate this, consider that I wanted to display the faculty advisor report as shown in the Introduction. To accomplish this I'd use the following SQL query:

SELECT f.Name as FacultyName, s.Name as StudentName FROM Faculty f LEFT JOIN Students s ON f.FacultyID = s.AdvisorID

This would return at least as many records as there are in the Faculty table, more if there are faculty that have more than one advising student. For example, with the report displayed earlier, the results from SQL with the above query would be:

FacultyName StudentName

Scott John

Scott Paul

Scott George

Scott Ringo

Jisun Tito

Jisun LaToya

Jisun Michael

Jisun Janet

Sam Carl

Sam Johann

Sam Christoph

Dave NULL

(The color differences in the results provide a visual cue, showing where each new faculty's set of advised students begins/ends.)

In our server-side code, we could loop through the results and craft the appropriate output. The pseudo-code might look like:

LastFaculty = '' StudentList = '' FirstTimeThrough = True For Each Record in ResultSet If LastFaculty <> Record.FacultyName Then // We have a new faculty member If FirstTimeThrough = False Then ... 'Work' with LastFaculty and StudentList ... // (be sure to trim off last ', ' from StudentList) End If LastFaculty = Record.FacultyName StudentList = '' End If If Record.StudentName IS NOT NULL Then StudentList = StudentList + Record.StudentName + ', ' End If FirstTimeThrough = False Next

As this pseudo-code shows, a loop is performed through the entire resultset and two variables are used to keep track of the faculty and the students he's advising. Essentially we need to keep track of some bit of information that uniquely identifies each faculty (their name, in this case, although that might not be unique in a real-world example); this piece of information is held in the LastFaculty variable. Then, for each record we see if the faculty name has changed. If so, we can 'work' with the faculty name and his comma-delimited student list. ('Working' with this data may mean displaying it or saving it to some business object or something else...) If there's a new faculty, the LastFaculty variable is updated to the new faculty's name and the variable holding the list of studnets (StudentList) is cleared out. Each loop through, regardless if there's a new faculty, appends the student's name to the StudentList variable.

Creating the Comma-Delimited List using SQL Syntax
I personally prefer to create the comma-delimited list in the 'client' rather than at the database level. (The 'client' here being the server-side web programming technology, not the end user's browser.) I feel as if I have more control over the content and its formatting at the 'client' level. However, there are times when I need the data directly from the database server in a specified format. For example, the web application may use Crystal Reports that display data culled directly from the database. In those cases, I need to provide a stored procedure or view that can be used as the Crystal Report's data source that provides the data in the appropriate, comma-delimited format.

There are a couple of approaches to accomplish this - one I was aware of from past projects and one I learned of recently, thanks in large part to the [sql-design] listserv on SQLAdvice.com (thanks to Erhan Hosca and Matthew Tamm). Furthermore, both approaches are discussed in this SQLTeam.com article: Using COALESCE to Build Comma-Delimited Strings.

Both approaches take in a given record and return the comma-delimited list of related records. That is, both approaches use a SQL query similar to the following:

SELECT s.Name as StudentName FROM Students s WHERE s.AdvisorID = FacultyID

To get the list of students Sam advises, you'd need to put in for FacultyID the value of professor Sam's FacultyID.

The first approach uses a CURSOR to step through the results of the above query one record at a time, crafting a comma-delimited string. I'm not going to focus on this approach - for more details refer to the SQLTeam.com article.

The second approach builds up a variable's value. The COALESCE SQL function is used to appropriately add the commas in the list. (COALESCE takes in a variable number of input parameters and returns the first non-NULL value in the list; see the technical documentation for more information.) The query goes like so:

DECLARE @StudentList varchar(1000) SELECT @StudentList = COALESCE(@StudentList + ', ', '') + s.Name FROM Students s WHERE s.AdvisorID = FacultyID SELECT @StudentList

This query builds up the value of the @StudentList variable based on the records returned by the query. For the first record returned, @StudentList + ', ' is NULL, so '' is returned by COALESCE and concatenated with the student's name. On subsequent records, @StudentList is non-NULL so @StudentList + ', ' takes the current list of students, appends a comma, and then appends the current student's name. The end result is that when the query completes, @StudentList contains a comma-delimited list of students that have the specified faculty member as their advisor.

What's nice about this approach is that it can be placed within a user-defined function (UDF). (See Creating a User Defined Function in SQL for Comma-Delimited Searches for more information on working with UDFs in SQL Server.) Once this logic is placed within a UDF, you can call the UDF from a SQL query. For example, imagine that I have created the following UDF (named udf_GetStudentsBeingAdvised) that takes in as input a FacultyID and returns a comma-delimited list of students being advised by the passed-in faculty:

CREATE FUNCTION dbo.udf_GetStudentsBeingAdvised(@FacultyID int) RETURNS VARCHAR(1000) AS BEGIN DECLARE @StudentList varchar(1000) SELECT @StudentList = COALESCE(@StudentList + ', ', '') + s.Name FROM Students s WHERE s.AdvisorID = @FacultyID RETURN @StudentList END

Armed with this UDF, I could now run the following SQL query, which would return me each faculty member along with a comma-delimited list of students she advises. This query would return the precise output shown at the very beginning of this article:

SELECT f.Name as FacultyName, dbo.udf_GetStudentsBeingAdvised(FacultyID) as StudentsAdvising FROM Faculty f

What About Many-to-Many Relationships?

In this article we saw how to create comma-delimited lists from a one-to-many relationship (specifically a one-to-many relationship from faculty to students). But what about many-to-many relationships? What if we wanted to display a list of students, and for each student display a comma-delimited list of classes? To accomplish this you'd simply need to adjust your SQL statement so that it did two joins - one to Enrollments and another to Classes, a la:

DECLARE @ClassList varchar(1000) SELECT @ClassList = COALESCE(@ClassList + ', ', '') + c.Name FROM Students s LEFT JOIN Enrollments e ON e.StudentID = s.StudentID INNER JOIN Classes c ON c.ClassID = e.ClassID WHERE s.StudentID = StudentID RETURN @ClassList

Conclusion
In this article we examined two ways to retrieve a comma-delimited list of related records - through the 'client' code and through a SQL query. The former approach involves writing logic in your server-side web application, looping through the records returned by the database and intelligently crafting the comma-delimited list. The latter approach involves using a clever SQL query, one that can even be put in a UDF to enable it to be called directly from a standard SELECT statement.

Happy Programming!

By Scott Mitchell