fredag 4 april 2008

Comma separated string of values...

/*
Note that if the values that are supposed to be comma separated can contain
comma signs it is a good idea to replace those with something else before
generating the list
*/

declare @list nvarchar(max)
set @list = ''
select @list = @list + ',' + replace(ColumnName,',','.') from TableName
set @list = substring( @list , 2, 10000000 )
select @list

--Simpler versions when handling id's
--Quite quick
declare @list varchar(max)
set @list = ''
select @list = @list + ',' + convert(varchar(10), ColumnName) from TableName
set @list = substring( @list , 2, 10000000 )
select @list

--Much quicker version (notable only when generating very long lists)
declare @list varchar(max)
set @list = (select ',' + convert(varchar(10), ColumnName) from TableName for xml path(''))
set @list = substring(@list , 2, 10000000 )
select @list