Last year I worked at a client site writing imports into the database for an Ascent CRM system. The import process was being implemented using Sql 2000 stored procedures. After getting used to a C# environment with source control and unit testing, working entirely in T-SQL was like going back to the 1970s. Still, the client had their reasons; although the job could have been done in C#, the charity in question figured that T-SQL would be cheaper to maintain in the long run, with programmers easier and cheaper to find.
So during my months of retro programming, I stumbled upon a SQL-2000 feature I hadn't seen before ... the ability to concatenate strings from multiple rows of a results set without using a cursor. It works like this:
The SET operator, as well as appearing before a SELECT statement, can also appear implicity inside a SELECT statement (and hence be used to set multple variables) like this:
declare @name varchar(40)
declare @id varchar(11)
select top 1 @name = au\_lname,
@id = au\_id
from authors
print @name
print @id
So far, so normal. But it turns out you can also aggregate the SQL variable to itself, like this:
declare @combined varchar(2000)
select @combined = @combined + au\_lname
from authors
where au\_lname like 's%'
print @combined
The example above demonstrates the syntax, but won't actually work, due to NULLs. The variable is NULL to begin with, so every attempt to concatenate onto the end of it also returns a NULL. Here's a proper example:
declare @combined varchar(2000)
select @combined = isnull(@combined + ', ','') + au\_lname
from authors
where au\_lname like 's%'
print @combined
This will concatenate the last names from all the authors returned, and also put commas in between. If I'd known that before writing the first import, I could have saved myself a whole load of messing about with cursors.
(OK, some developers will point out that this sort of thing should be done in the presentation layer, but as I said, it was a 1970's style assignment - there was no presentation layer.)
If you're running SQL 2005, there are other ways of doing it, using FOR XML PATH or CROSS APPLY - see the second half of this article on aspfaq.com for details.