A while ago, I needed a function that returned a concatenated string of the values found in multiple rows of a SQL table. For example, given that a project can have multiple team members, you would end up with a team member table keyed on, say, the project and the person. So, I wanted to write a function that I could use within a select statement that returns the project and a concatenated listing of all team members for the project.
The main looping construct in SQL, cursors, of course, suck. So I called my SQL guru (I’ll call him "A.J.") which is normally what I do when I get stumped and need to bounce ideas around. We then came up with a solution that I’ll describe below.
Given the table TeamMember(project_id, person_name) the following code will return a concatenated listing of team members (assuming @id was passed into the function):
declare @s varchar(1000)
set @s = ''
select @s = @s + person_name + ' ' from team_member where project_id = @id
return @s
I have to admit, I didn’t think this would work because I thought SQL server would only return one row. For example:
declare @s varchar(1000)
set @s = ''
select @s = person_name from team_member where project_id = @id
return @s
Returns the "last" row for the given id.
I’m not sure if this is just an undocumented trick or a SQL hack. If SQL Server is supposed to be relational (*), you wouldn’t think this would work, right? The code above basically exposes the fact that the select statement is looping through the rows, and if it was relational this sort of stuff would be "hidden" from the "user" (in this case programmer).
(*) SQL Server, by the way, is not really relational. www.dbdebunk.com offers plenty of reasons why.
This method works for other types of aggregation as well. For example, we could use the same method to sum up the numbers in a table. Given the table some_table(some_number), we could return the sum of the numbers via:
declare @s int
set @s = 0
select @s = @s + some_number from some_table
return @s
(The puns, by the way, are intended)
To do an unscientific performance test, I created a table loaded with a million numbers and summed them via a) the TSQL sum aggregate function, b) the method above and c) a cursor. On my (old) laptop, the sum function took 1,350 milliseconds, the method above took 1,213 milliseconds, and the cursor took 67,206 milliseconds.
I would have expected the sum function to be the fastest, the method above to be slower and then the cursor being dramatically slower. As you can see, the method above is slightly faster, although the difference is negligible and in other tests I have conducted it was slightly slower than the sum function (but not nearly as slow as the cursor).