I have seen this done so many different ways, most of them badly or at least not very elegantly.
So what is the problem?
Well, it is simple enough. You have a recordset that has a column that you need (for whatever reason) to turn into a comma separated list.
For the purposes of this example, I am going to be working with just a simple single column table. You can knock it up with the following code
-- Suppress data loading messages SET NOCOUNT ON -- Create Sample Data CREATE TABLE Info ( ID int ) -- Load Sample Data INSERT INTO Info VALUES ( 1 ) INSERT INTO Info VALUES ( 2 ) INSERT INTO Info VALUES ( 3 ) INSERT INTO Info VALUES ( 5 ) INSERT INTO Info VALUES ( 8 )
The most common way I have seen is the following
DECLARE @str VARCHAR(max) -- Coalesce into a nice string SELECT @str = COALESCE(@ str, '') + cast(ID as varchar(20)) + ',' from Info -- Remove the trailing comma. SET @str = LEFT(@str, LEN(@str) - 1) SELECT @str
Now, this is good, but it always looks just a bit messy. Having to trim that last comma off is just unnecessary if you think about it for a bit. Now that your really thinking about it, you will also notice that this will die badly if our table is empty as the LEFT will just collapse.
So, lets write something simpler…
DECLARE @str varchar(max) SELECT @str = COALESCE(@str + ', ', '') + CAST(ID as VARCHAR) FROM Info SELECT @str
Now wasn’t that much neater?