4

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!

Frank V
  • 25,141
  • 34
  • 106
  • 144

2 Answers2

10

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

Community
  • 1
  • 1
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • 1
    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 Feb 11 '09 at 01:56
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,'')
Paul
  • 96
  • 2
  • 1
    I don't think this will work for SQL server 2000. You'd probably need 2005 or 2008 for this to work... – Frank V Feb 19 '09 at 19:32