Implode type function in SQL Server 2000?

Question

Is there an Implode type function for SQL Server?

What I have is a list (in a SQL server table):

 Apple
 Orange
 Pear
 Blueberry

and I want them to come out as

 Apple, Orange, Pear, Blueberry

I hope for the space and comma to be configurable but I can always replace it if it isn't...

Quick help would be appreciated!

Answer 1

There are some questions related to this already on SO (search for PIVOT or UNPIVOT or GROUP_CONCAT), but a simple solution for SQL Server (using a trick with variable concatenation) for your stated problem:

DECLARE @str AS varchar(MAX)
DECLARE @separator AS varchar(50)
SET @separator = ', ' -- Here's your configurable version

SELECT @str = COALESCE(@str + @separator, '') + <column_name>
FROM <table_name>
ORDER BY <sort_order>

Of course, if this is needed on a per-row basis, you can use UDFs or the really cool FOR XML trick

Answer 2

I typically use the FOR XML PATH method for this, it works for row subqueries as well, simply

SELECT ', ' + <column_name>
FROM <table_name>
ORDER BY <sort_order>
FOR XML PATH('')

This will give you your list with a ", " at the start, and the quickest way to remove that
is to use stuff

SELECT STUFF((
        SELECT ', ' + <column_name>
        FROM <table_name>
        ORDER BY <sort_order>
        FOR XML PATH('')
    ),1,2,'')