Skip to main content

Sql 2000 Concatenation Trick

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.

Comments

Anonymous said…
You can also set the value on the @combinded like '' to begin with like this.

declare @combined varchar(2000)
set @combinded = ''
select @combined = @combined + au_lname
from authors
where au_lname like 's%'
print @combined

Popular posts from this blog

SSRS multi-value parameters with less fail

SSRS supports multi-value parameters, which is nice, but there are a few issues with them. This is how I deal with them. Two of the problems with SSRS multi-value parameters are: You have to jump through a few hoops to get them to work with stored procedures The (Select All) option, as shown above The reason the (Select All) option is a problem is that it is a really inelegant way of saying 'this parameter does not matter to me'. If you have a list with hundreds of values, passing all of them as a default option just seems wrong. Also, if your report shows the user which items they selected, printing the whole list when they choose (Select All) is excessive. So in this post I'm going to show my particular way of: Jumping through the hoops to get Multi-Value params in a stored procedure Adding a single '--All--' value that the report interprets as meaning all the options. Getting Multi-Value params to work with Stored Procedures This is

Copying data to Salesforce Sandboxes using TalenD

A common problem with Salesforce Developer Sandboxes is that they are blank. Really you're going to want some data in there, so there are various strategies for copying data from your live instance to the Sandbox. There are some paid-for solutions - SFXOrgData , Salesforce Partial Data Sandboxes - but if you've got a decent ETL tool you can build your own. There are a bunch of free ETL tools for Salesforce: JitterBit Data Loader is good for quick ad-hoc tasks but the free version makes it difficult to manage specific ETL projects or share projects with other users Pentaho Community Edition - an open source edition of the enterprise version Apatar was a free open source Salesforce ETL which still works but development seems to have stopped since 2011 TalenD Open Studio is an open source ETL tool For the task of copying data from live to a Sandbox, either Pentaho or TalenD Open Studio could be used, depending on preference. Here's a good comparison of the dif

Bug Hunter in Space

In 1987, Acorn launched the Archimedes home computer. At the time, it was the fastest desktop computer in the world, and at the time, I was fortunate enough to have one to experiment with. The Archimedes was great, but it never really took off commercially. However, it was built around the ARM processor, which Acorn had designed itself when it could not find any existing processors suitable for its 32-bit ambitions. The ARM processor was a masterpiece of simple and intuitive design, and its still around today, with most of the instruction set pretty much unchanged. In fact, you've probably got one in your pocket right now. Its design makes it process very efficiently on low energy intake, and hence it is estimated that about 98% of all mobile phones contain an ARM chip. Over 10 billion ARM chips have been shipped, and they outnumber Intel's long running x86 series of chips by a factor of about 5 to 10. I had learned programming on the BBC Model B , and when we got the A