Converting a Column to a delimited string

Well, after a good bit of searching, I found a couple ways to convert a column of data to a delimited string. It's actually not too bad.

DECLARE @p_str VARCHAR(2000)
SET @p_str = ''
SELECT @p_str = CAST(O.OrderID AS NVARCHAR(6)) + ',' + @p_str
FROM (select OrderID
from Orders
where CustomerNumber = @CustomerNumber
) O


You have to set the @p_str to '' or it'll return a null. The other thing is that this method ends with a , that you may or may not have to trim.

The other method is this:

DECLARE @p_str VARCHAR(2000)
SELECT @p_str = COALESCE(@p_str + ', ', '') + CAST(OrderID AS varchar(5))
FROM Orders
WHERE CustomerNumber = @CustomerNumber

The second method does not need to have the @p_str set to '', and it doesn't end with a comma. So in the end, the second is probably the better method.