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:

• When processing the first row, the variable is null, so the second term of the coalesce function appends the "empty string" to the variable, then the value from the first row is appended
• All subsequent rows are handled by the first term of the coalsce function, resulting in the delimiter being appended to the variable prior to adding the next row's value.

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!