How to make a list of T-SQL results with comma's between them?

Question

Suppose we have a simple query like this:

SELECT x 
FROM t
WHERE t.y = z

If we have one record in the result set, I want to set variable @v to that one value. If we have two or more records, I'd like the results to be separated by a comma and a space. What is the best way to write this T-SQL code?

Example:

result set of 1 record:

Value1

result set of 2 records:

Value1, Value2

result set of 3 records:

Value1, Value2, Value3

`What is the best way?` "Best" is subjective... depends on what things you are looking for. Can you be more specific? Does it *need* to be done entirely in T-SQL or could you use a solution on the client? — Mark Byers, Mar 15 '12 at 19:22
Does this apply? http://stackoverflow.com/questions/1048209/concatenating-column-values-into-a-comma-separated-list — MatthewMartin, Mar 15 '12 at 19:23
I'm just wanting to write a generic function for this purpose .. normally I handle these in .NET code after I've gotten the results, but I'd like to do it in SQL — JustBeingHelpful, Mar 15 '12 at 19:23
possible duplicate of [Concatenate many rows into a single text string?](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — Lamak, Mar 15 '12 at 19:28
@MacGyver Doing it in .NET is probably a better approach, although the answer I provided will work. T-SQL is notoriously *bad* at string processing. — Yuck, Mar 15 '12 at 19:28

score 10 · Accepted Answer · answered Mar 15 '12 at 19:27

this will give you the list of values in a comma separated list

create table #temp
(
    y int,
    x varchar(10)
)

insert into #temp values (1, 'value 1')
insert into #temp values (1, 'value 2')
insert into #temp values (1, 'value 3')
insert into #temp values (1, 'value 4')

DECLARE @listStr varchar(255)

SELECT @listStr = COALESCE(@listStr+', ', '') + x
FROM #temp
WHERE #temp.y = 1

SELECT @listStr as List

drop table #temp

score 9 · Answer 2 · answered Mar 15 '12 at 19:25

9

You can use XML to do that:

DECLARE @V VarChar(4000);

SELECT @V = CONVERT(VarChar(4000), (
  SELECT x + ', '
  FROM t
  WHERE t.y = z
  FOR XML PATH('')
));
-- To remove the final , in the list:
SELECT @V = LEFT(@V, LEN(@V) - 2);

SELECT @V;

For other options check out Concatenating Row Values in SQL.

answered Mar 15 '12 at 19:25

Yuck

49,664
13
105
135

+1, this works. If you want to remove the last comma just do Select LEFT(@v, Len(@v)-1) – Ta01 Mar 15 '12 at 19:28
@kd7 Thanks - I'll edit that in. It's actually `- 2` though, since the value is `, ` which is comma + space. – Yuck Mar 15 '12 at 19:29

David · Answer 3 · 2013-12-31T16:25:05.443

3

Since it's SQL Server 2008, you can use FOR XML:

SELECT SUBSTRING(
    (SELECT ',' + t.x
     FROM t
     WHERE t.y = z
     FOR XML PATH('')),
    2,
    200000) AS CSV

FOR XML PATH('') selects the table as XML, but with a blank path. The SUBSTRING(select, 2, 2000000) removes the leading ', '

edited Dec 31 '13 at 16:25

answered Mar 15 '12 at 19:30

David

1,143
7
9

score 1 · Answer 4 · edited Jul 25 '14 at 10:35

1

How about something like this???

DECLARE @x AS VARCHAR(2000)
SET @x = ''
SELECT @x = @x + RTRIM(x) + ','
FROM t
SELECT @x = SUBSTRING(@x, 1, LEN(@x) - 1)
PRINT @x

edited Jul 25 '14 at 10:35

Gouri Shankar Aechoor

1,561
1
8
8

answered Mar 15 '12 at 19:28

larryr

1,536
3
17
27

score 1 · Answer 5 · edited Mar 15 '12 at 19:38

You could use a recursive CTE for this:

CREATE TABLE #TableWithId (Id INT IDENTITY(1,1), x VARCHAR)

INSERT INTO #TableWithId
SELECT x 
FROM t
WHERE t.y = z

WITH Commas(ID, Flattened)
AS
(
-- Anchor member definition
    SELECT ID, x AS Flattened
    FROM #TableWithId
    WHERE ID = 1
    UNION ALL
-- Recursive member definition
    SELECT #TableWithId.Id, Flattened + ',' + x
    FROM #TableWithId
    INNER JOIN Commas
        ON #TableWithId.Id + 1 = Commas.Id
)
-- Statement that executes the CTE 
SELECT TOP 1 Flattened
FROM Commas
ORDER BY id;
GO

How to make a list of T-SQL results with comma's between them?

5 Answers5

Linked