Get a comma separated list

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.

2015-06-10 17_20_39-SQLQuery4.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (62))_ - Microsoft SQL Server

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

4 Responses to Get a comma separated list

  1. Mike Lewis says:

    Word of caution on this – using a variable in the SELECT clause is not supported and won’t always produce the result you want. It depends on what order the various items in your query plan are executed. Sometimes, it may just return the last value rather than concatenating.

    A better method is to use XML (see http://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/ as an example).

  2. I see the SELECT syntax frequently. Is there a a link to a page that discusses the issue?

  3. Mike Lewis says:

    Here is an old KB article explaining it: https://support.microsoft.com/en-us/kb/287515

    It’s still very much valid though.

    • way0utwest says:

      Thanks, Mike. Haven’t had time to look for this.

      There are certainly things to watch out for, but I’m not sure this will disappear anytime soon. There is a lot of existing code that would break and Microsoft seems very, very hesitant to break old code. Even at the extent of not moving forward.

Comments are closed.