How to make a list of T-SQL results with comma's between them?
Solution 1
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
Solution 2
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.
Solution 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 ', '
Solution 4
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
Solution 5
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
![MacGyver](https://i.stack.imgur.com/UUIk1.jpg?s=256&g=1)
MacGyver
My friends call me "Mac". I'm a master of improvisation. I have vast scientific knowledge and unique abilities to use ordinary objects to get myself and friends out of trouble. I typically carry my Swiss Army knife and a roll of duct tape with me at all times. I dislike guns because of a traumatic childhood incident. I try to avoid violence whenever possible. Because my life was getting too stressful at the Phoenix Foundation, I have picked up programming as a new career. I spend my spare time on Stack Overflow.
Updated on June 05, 2022Comments
-
MacGyver about 2 years
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
-
Ta01 over 12 years+1, this works. If you want to remove the last comma just do Select LEFT(@v, Len(@v)-1)
-
Yuck over 12 years@kd7 Thanks - I'll edit that in. It's actually
- 2
though, since the value is,
which is comma + space.