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

sql sql-server-2008 tsql comma
MacGyver picture MacGyver · Mar 15, 2012 · Viewed 12.8k times · Source

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

Answer

Taryn picture Taryn · Mar 15, 2012

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

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How can I select the first day of a month in SQL?

I just need to select the first day of the month of a given datetime variable. I know it's quite easy to do using this kind of code: select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) + '/' + CAST(MONTH(@mydate) …

Read More
SQL Server SELECT INTO @variable?

I have the following code in one of my Sql (2008) Stored Procs which executes perfectly fine: CREATE PROCEDURE [dbo].[Item_AddItem] @CustomerId uniqueidentifier, @Description nvarchar(100), @Type int, @Username nvarchar(100), AS BEGIN DECLARE @TopRelatedItemId uniqueidentifier; SET @TopRelatedItemId = ( SELECT top(1) RelatedItemId FROM …

Read More