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?

If you want your application to scale, it would be better to do this kind of thing outside the database. The database will almost always be your bottleneck. — Joseph Bui, Oct 07 '08 at 19:51
@Joseph Bui - Believe me, I know. Unfortunately the project lead insists I do it this way. — Joshua Carmody, Oct 07 '08 at 20:27

score 99 · Answer 1 · edited Apr 18 '13 at 11:31

99

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

edited Apr 18 '13 at 11:31

Tony

9,280
3
46
72

answered Oct 07 '08 at 20:55

Joshua Carmody

13,040
15
65
82

I really like this solution the most. Easy to use and only one line to add in any SQL. – Tai Kahar Oct 23 '12 at 09:30
2

Note: This method causes special characters to be replaced by their XML equivalent. IOW, Barnes & Noble becomes Barnes & Noble – SvdSinner Apr 30 '18 at 20:31

score 31 · Accepted Answer · answered Oct 07 '08 at 19:48

31

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)

answered Oct 07 '08 at 19:48

Ben Hoffstein

100,255
8
102
119

Is it me or doesn't this work on a linked server? When i try something like `SELECT @List = @List + CONVERT(varchar, ID) + ',' FROM LinkedServer.MyDatabase.dbo.MyTable` it always returns only one ID. A `Select *` returns many ID's. – Martin Nov 05 '14 at 13:42

score 15 · Answer 3 · answered Oct 08 '08 at 03:40

15

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

answered Oct 08 '08 at 03:40

Cade Roux

85,870
40
177
264

score 13 · Answer 4 · answered Jul 11 '18 at 17:27

13

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

answered Jul 11 '18 at 17:27

Carter Medlin

11,137
4
59
66

score 1 · Answer 5 · answered Oct 07 '08 at 19:47

1

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

answered Oct 07 '08 at 19:47

Peter

3,531
5
29
43

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

5 Answers5

Linked

Related