Set-based string concatenation
Today, one of my colleagues asked me how to concat numbers from one column into a single string not using cursors. Motivated by his question I decided to write my first Sql-related post.
I’ll present 2 or 3 methods to do this, but first create our sample table:
use tempdb; create table test ( number int ); insert test values (1); insert test values (2); insert test values (3); insert test values (11); insert test values (6);
1. First method requires a variable to which the numbers will be appended.
declare @text varchar(100); set @text = ''; select @text = @text + ',' + cast( number as varchar(11) ) from test order by number; select stuff( @text, 1, 1, '' );
We must initialize it with an empty string, otherwise we’ll get null ( unless CONCAT_NULL_YIELDS_NULL is off ). Then we simply remove the preceding comma and our string is ready.
declare @text varchar(100); select @text = coalesce( @text + ',', '' ) + cast( number as varchar(11) ) from test order by number; select @text;
3. Both of the presented techniques works in sql 2000, however in sql 2005 we can make use of the new FOR XML PATH clause:
select stuff( (select ',' + cast( number as varchar(11) ) as 'text()' from test order by number for xml path('') ), 1, 1, '' );
We specify row element to be empty ( path(”) ) and the number to be treated as a text node. At the end we only need to remove the first comma. Thanks to this trick we’ll get what we need.