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
– Joseph Bui, Commented Oct 7, 2008 at 19:51
@Joseph Bui - Believe me, I know. Unfortunately the project lead insists I do it this way. — Joshua Carmody
– Joshua Carmody, Commented Oct 7, 2008 at 20:27

Glorfindel · Accepted Answer · 2022-08-06 14:04:12Z

105

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

edited Aug 6, 2022 at 14:04

Glorfindel

22.8k13 gold badges97 silver badges124 bronze badges

answered Oct 7, 2008 at 20:55

Joshua Carmody

13.8k16 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tai Kahar Over a year ago

I really like this solution the most. Easy to use and only one line to add in any SQL.

SvdSinner Over a year ago

Note: This method causes special characters to be replaced by their XML equivalent. IOW, Barnes & Noble becomes Barnes & Noble

Ben Hoffstein · Accepted Answer · 2008-10-07 19:48:10Z

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 7, 2008 at 19:48

Ben Hoffstein

104k8 gold badges107 silver badges121 bronze badges

1 Comment

Martin Over a year ago

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.

Carter Medlin · Accepted Answer · 2018-07-11 17:27:16Z

28

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

answered Jul 11, 2018 at 17:27

Carter Medlin

12.6k5 gold badges71 silver badges71 bronze badges

Comments

Cade Roux · Accepted Answer · 2008-10-08 03:40:23Z

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 8, 2008 at 3:40

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

Comments

Peter · Accepted Answer · 2008-10-07 19:47:19Z

2

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 7, 2008 at 19:47

Peter

3,6035 gold badges34 silver badges44 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related