Turning a Column into a List

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?

Advertisements