SQL Tricks: Making a Comma Delimited List from a Column in a Table

So, as I was writing the stored proc for a label report, I ran into this situation:  The users would be selecting from a list of possible values in the UI.  When the UI passes that selection back to the database to be run by the Proc, that list gets converted into a list of IDs.  In the stored proc, that list of IDs is exploded back out into a table to get the related information for each ID that the user picked.  However, one of the requirements is that the output from the stored proc contain the plain text list of all of the Name fields associated with those selected IDs.  So, I was had to find a way to take the ‘Name’ column from that exploded table and display every name in the table as a comma delimited list.  As an added twist, the output from each of those names had to be trimmed down to 5 characters, due to space requirements on the finished report.

Let me start by showing how I exploded the list of IDs out into it’s own table, then I’ll show how I collapsed that table back down into a list of names:

   1: --The Stored Proc accepts a list of IDs as an XML String.  I've recreated it here

   2: DECLARE @GradingPeriodID XML

   3: SET @GradingPeriodID = '<Root><Item id="1" /><Item id="2" /><Item id="3" /><Item id="4" /></Root>'

   4:  

   5: --Reading the XML of GradingPeriodIDs into the Temp Table

   6: DECLARE @GradingPeriodIds AS TABLE(Id INT NOT NULL PRIMARY KEY CLUSTERED(Id))

   7: INSERT INTO @GradingPeriodIDs(Id)

   8: SELECT GradingPeriod.id.value('@id', 'SMALLINT')

   9: FROM @GradingPeriodID.nodes('/Root/Item') GradingPeriod(id)

  10:  

  11: --Semicolon is required for CTE

  12: ;

  13: WITH

  14:  

  15: --This CTE exploedes the list of IDs into relevant data

  16: SelectedGradingPeriods

  17: AS

  18: (

  19:     -- No more than 4 grading periods are reported for any label size

  20:     SELECT TOP 4

  21:          SelectedGradingPeriodSequence        = ROW_NUMBER() OVER(ORDER BY gp.StartDate)

  22:         ,GradingPeriodID                    = gp.GradingPeriodID

  23:         ,GradingPeriodCode                    = gp.Code

  24:         ,TermID                                = gp.TermID

  25:         ,AcadSessionID                        = gp.AcadSessionID

  26:         ,GradingPeriodName                    = gp.Name

  27:     FROM

  28:          dbo.GradingPeriod gp

  29:             --LEFT JOIN dbo.StudentGradingPeriod sgp ON sgp.GradingPeriodID = gp.GradingPeriodID

  30:     WHERE

  31:          gp.AcadSessionID = @AcadSessionID

  32:          AND ((@GradingPeriodID IS NULL) OR

  33:               EXISTS (SELECT * FROM @GradingPeriodIds gpi WHERE gpi.Id = gp.GradingPeriodID))

  34:     ORDER BY

  35:         gp.StartDate

  36: )

  37:  

  38: --Here we select the list of Grading Period Names from the CTE

  39: SELECT GradingPeriodList = (

  40:     SELECT SUBSTRING(

  41:         (SELECT ', ' + LEFT(s.GradingPeriodName, 5) 

  42:          FROM SelectedGradingPeriods s FOR XML PATH('')),3,20000) )

Everything above line 38 is background setup for the actual trick of building the comma separated list.  That is done entirely in lines 39-42.  This is done using a couple of neat tricks.

The first trick I use is the ‘FOR XML PATH('')’ query.  Normally, that query is used to build simple XML output code, with the wrapper tag being provided by the parameter passed to the PATH() function.  However, when you structure that query and pass in an empty string, the output comes out without any kind of wrapper around it, and you’re left with a string of values.

The next trick you see there is the SUBSTRING command, followed by the numbers 3, 20000.  That’s a little tricky, but here’s what it does.  If you notice, the query is a concatenation of ', ' and the grading period name.  This concatenation forces a comma to be inserted between every element in the list.  However, that means that the list will start with a comma, instead of the first value in the list.  That’s why we take a substring, starting from the 3rd character and going out for 20000 characters (you can change this value to whatever you need it to be, but setting a very large value ensures you won’t clip your own data).  That substring command effectively trims away the leading comma.

The last part of that query is the LEFT command, which grabs the left most X characters of the value being selected.  In this case, I’ve specified 5.

Combining those 3 tricks together into those 4 lines of code effectively take an entire column of of a table and condense it down into a comma separated list.