Creating a Delimited list from a column of a table

Many times when we normalize our database, we end up storing multi-values attributes as separate rows in table. For eg. Consider we are storing Photos and a set of tags associated with the photos.

Assume the following table structure

Now while retrieving the tags for photos we need only one record per photo with all the tags as comma separated in the output.

Script to create sample data



Solution In SQL Server 2005 Onwards


Notice the Innermost REPLACE function REPlACE(Tag,’ ‘,’#@#’) is to escape spaces in the tag values, since the 2nd REPLACE function is replacing space with the delimiter. Hence we need the Outermost REPLACE function to replace the escape sequence with a space.

Solution In SQL Server 2000

In SQL Server 2000, we need to create a function which would accept photo_id as input param and as output would generate the delimited string of tags for that photo_id.

Cleanup script