How can I combine multiple rows into a comma-delimited list in SQL Server 2005?

Question

Right now, I have a SQL Query like this one:

SELECT X, Y FROM POINTS

It returns results like so:

X    Y
----------
12   3
15   2
18   12
20   29

I'd like to return results all in one row, like this (suitable for using in an HTML <AREA> tag):

XYLIST
----------
12,3,15,2,18,12,20,29

Is there a way to do this using just SQL?

Answer 1

Thanks for the quick and helpful answers guys!

I just found another fast way to do this too:

SELECT  STUFF(( SELECT ',' + X + ',' + Y
                FROM Points
              FOR
                XML PATH('')
              ), 1, 1, '') AS XYList

Credit goes to this guy:

Link

Answer 2

DECLARE @XYList varchar(MAX)
SET @XYList = ''

SELECT @XYList = @XYList + CONVERT(varchar, X) + ',' + CONVERT(varchar, Y) + ','
FROM POINTS

-- Remove last comma
SELECT LEFT(@XYList, LEN(@XYList) - 1)

Answer 3

Starting in SQL 2017, you can use STRING_AGG

SELECT STRING_AGG (X + ',' + Y, ',') AS XYLIST
FROM POINTS

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017

Answer 4

Using the COALESCE trick, you don't have to worry about the trailing comma:

DECLARE @XYList AS varchar(MAX) -- Leave as NULL

SELECT @XYList = COALESCE(@XYList + ',', '') + CONVERT(varchar, X) + ',' + CONVERT(varchar, Y)
FROM POINTS

Answer 5

DECLARE @s VarChar(8000)
SET @s = ''

SELECT @s = @s + ',' + CAST(X AS VarChar) + ',' + CAST(Y AS VarChar) 
FROM POINTS

SELECT @s 

Just get rid of the leading comma