Generating a Comma Separated List with an Implicit Recursion

As Dabblers, we love our data structured, ordered and indexed, but occasionally this runs up against the requirements of the data's consumers.  Developers who access our Microsoft SQL Servers will insist on working with comma separated lists, chopping up and concatenating data in their own, substandard, non-relational way.  In order to avoid dark mutterings, and to persuade them to continue to make us coffee, it is often useful to indulge them.

Returning nice structured recordsets as ugly comma (or tab, or bar) separated lists can seem onerous, and seems like a good candidate for a cursor.  As usual, cursors are the last things you need.  Instead, you can use a neat little trick that creates, in effect, a recursive structure that iterates through a recordset, by referencing a field within that recordset as a variable.

Here's some code:

Firstly, let's create some data


CREATE TABLE CSLTest (ID int, vval varchar(20))
GO
INSERT INTO CSLTest VALUES(1, 'abc'),(2, 'def'), (3, 'ghi')
GO

And here's the (rough and ready) code to get the comma separated list:


DECLARE @ CSLString  varchar(5000)
SELECT @ CSLString  = isnull(@ CSLString  + ', ','') + vval
FROM
(SELECT vval from CSLTest) A
SELECT @CSLString 

Let's walk through this, Dabblers.  There are two key elements here, both of them SELECT statements.  The inner select retrieves the main recordset that we will be working with. Crucially, however, it wraps it in parentheses, and gives it an alias.  Any use of Microsoft SQL Server will be familiar with aliasing, but some may not realise that it allows us to treat the aliased data as a single lump, even if it contains multiple rows.

The top level select is an example of us doing exactly this.  By referencing vval in the aliased data, we iterate through each record, and append it to the existing string (the isnull() is required to cope with the very first iteration, so that we don't add a comma at the beginning).

With a little bit of tweaking, we can put this functionality inside a User Defined Function.  This allows us to return the CSL from a function. Of course, most dabblers will be familiar with the Table Value functions available in newer versions of Microsoft SQL Server, but most developers have yet to see the light...


CREATE FUNCTION funReturnCSL
(@MinID int, @MaxID int)
RETURNS varchar(5000)
AS    BEGIN
      DECLARE @CSLString varchar(5000)
      SELECT @CSLString = ISNULL(@CSLString + ', ','') + vval
      FROM
      (SELECT vval FROM CSLTest WHERE ID BETWEEN @MinID AND @MaxID) A
      RETURN @CSLString
      END
GO

SELECT dbo.funReturnCSL(2,3)