I’m writing this post as a way to help motivate the #SQLNewBloggers out there. Read the bottom for a few notes on structuring a post.

I was working on a test of sorts and wanted to return multiple values as the output, but as a single variable. In other words, I couldn’t return a result set, I needed to return a string.

I knew this was easy, and decided this would make a nice simple blog. Here we go.

Let’s start with a simple table. Here’s one that has a few rows in it.

CREATE TABLE MyTest
( id int);
GO
INSERT mytest values (1), (2), (3);
go

I want to return the values “1, 2, 3” as a string, in any order. Here’s how it works:

DECLARE @i VARCHAR(MAX);
SELECT @i = COALESCE(@i + ‘, ‘,”) + CAST( Id AS VARCHAR)
FROM MyTest;
SELECT @i;

The COALESCE is important as the first time this runs, we have a NULL for the variable. In this case, we return an empty string. This is almost like the inverse of the operation that ends recursion. We add in the first row, and we end up with ‘1’ as the string.

Note: it could be 2 or 3 in the string as I don’t have an ORDER BY. DO NOT depend on the order of insertion in the table. If you care about ordering, always include an ORDER BY.

The next execution has a blank string (NOT NULL), so that is returned. In this case, we have ‘1’ + ‘, ‘ for the first part. The second part adds in the next row.

This continues, and I get a nice set of output.

SQLNewBlogger

This one was short. It took me almost as much time to write the code and find a reference as it did to write the post. Five minutes.

References

This is basic T-SQL, but here’s another look at this.

• http://blog.sqlauthority.com/2008/06/04/sql-server-create-a-comma-delimited-list-using-select-clause-from-table-column/