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:

http://geekswithblogs.net/mnf/archive/2007/10/02/t-sql-user-defined-function-to-concatenate-column-to-csv-string.aspx

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)