There is an even simpler and more elegant use the Coalsce() function to "Flatten" a column to a delimited string. I first saw the technique in an article in T-SQL Solutions. At first Glance, I could not believe that it would work, but it does and it performs better than using a cursor or while loop to perform the same operation:
@w_resulttext varchar(8000)
SELECT @w_resulttext = coalesce( @w_resulttext +'|', '') + Name
sysobjects where type = 'U'
@w_resulttext
The trick to this technique is to use the coalesce function to insert the delimiter. Here is how it works:
I had written a bunch of user-defined functions using cursors to "flatten" or "Pivot" data for several parent-child tables. To simplify converting them all to this technique, I wrote a helper proc to generate the code for the technique. It generates a functioning code "template" that can be used directly or modified by adding conditions or joins to other tables:
master
procedure sp__CreateFlattener
sysname
,@p_column sysname
,@p_delimeter varchar(20) = ','
,@p_SQL varchar(8000) = null OUTPUT
@p_SQL = 'Declare @w_resulttext varchar(8000)
+ char(39) + @p_delimeter + char(39) + ', ' + char(39) + char(39) + ') + ' + @p_Column
+ char(13) + char(10) + 'FROM ' + @p_table
+ char(13) + char(10) + 'select @w_resulttext'
@p_sql -- comment this out if you don't want the results printed to message window
0
declare @SQL varchar(8000)
sp__createflattener @p_table = 'sysobjects', @p_column = 'Name', @p_delimeter = '|', @p_SQL = @SQL OUTPUT
@sql
Enjoy!
Kindest Regards,
Clayton