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.
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).
LikeLike
I see the SELECT syntax frequently. Is there a a link to a page that discusses the issue?
LikeLike
Here is an old KB article explaining it: https://support.microsoft.com/en-us/kb/287515
It’s still very much valid though.
LikeLike
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.
LikeLike