Comma-delimited Column with SQL 2005

Long time no blog. The month of August is ridiculously busy at my job, so I haven’t really had a chance to post. Since this month is about over, I should be able to start posting regularly again. Luckily this past month I had the opportunity to learn quite a bit, particularly about fluent NHibernate. Most likely I will be posting about that topic in the near future…mainly to praise nhprof, which is a necessity if transitioning to NHibernate.

Okay, so let’s get started. I seem to always find myself needing a script that will create a comma-delimited column. Well, I found a nice post on the topic, but I didn’t like a hard-coded number he has in his script. So, I think I have a solution to the issue, but I’m curious to see what others think. So…here’s the SQL:

DECLARE @ENDIDENTIFIER CHAR
SET @ENDIDENTIFIER=';'

SELECT
REPLACE((SELECT many.Items + ','
         FROM many WHERE many.foreignKey = one.primaryKey 
         FOR xml path('')) + @ENDIDENTIFIER, ',' + @ENDIDENTIFIER, '') AS csv
FROM one 

Here’s the same script without the @ENDIDENTIFIER:

SELECT
REPLACE((SELECT many.Items + ','
         FROM many WHERE many.foreignKey = one.primaryKey 
         FOR xml path('')) + ';', ',;', '') AS csv
FROM one 

The @ENDIDENTIFER is just there so I don’t have to remember where to put my identifier to replace. This way it’s easy if I have a column that might have a ;, I can easily use another identifier.

This seems to perform well, but haven’t used it on anything with more than 50,000 records. If any of you decide to use it, I’d be curious to know if you improve it or if it performs well. Thanks for reading!