How can I combine multiple rows into a comma-delimited list in SQL Server 2005?
SqlSql ServerListFunctionConcatenationSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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:
Solution 2 - Sql
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)
Solution 3 - Sql
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
Solution 4 - Sql
Starting in SQL 2017, you can use STRING_AGG
SELECT STRING_AGG (X + ',' + Y, ',') AS XYLIST
FROM POINTS
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017
Solution 5 - Sql
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