views:

359

answers:

2

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!

+3  A: 

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

Cade Roux
Thank you! Sorry for the double post. I didn't know what to search under....
Frank V
I'll let you in on a secret - you're way better off searching on Google restricting to this site. http://www.google.com/search?q=site:stackoverflow.com+concatenate+sqlserver
Cade Roux
Thanks Cade, I will try that next time...
Frank V
A: 

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,'')
Paul
I don't think this will work for SQL server 2000. You'd probably need 2005 or 2008 for this to work...
Frank V