T-SQL: A Simple Way to Build a String from a Column

Monday, September 29, 2008

First, some test data:

declare @emp table ( name varchar(50) primary key )
insert @emp values
    ( 'Lino' ),
    ( 'John' ),
    ( 'Noel' )

Now, here's an example of the old, long way to do things. I'll use a custom while loop instead of cursors, because the cursor-based approach takes even more code than this:

select *
into #while_loop
from @emp

declare @name varchar(50), @str varchar(max)

while exists ( select * from #while_loop )
begin
    select top 1 @name = name
    from #while_loop
    
    delete #while_loop
    where name = @name
    
    set @str = case
        when @str is null
            then @name
        else @str + ',' + @name
    end
end

drop table #while_loop

print @str

And here's the new, short way to do the same thing:

declare @str varchar(max)

select
    @str = case
        when @str is null
            then name
        else @str + ',' + name
    end
from @emp

print @str

As you can see, this is a healthy 50% reduction in lines of code, all of which were bookkeeping for the while loop anyway. I don't know if this is documented in Books Online; I can't find it if it is.

MS SQL Server