SQL Query to List

I have a table vari开发者_如何学Cable in a stored procedure. What I want is to find all of the unique values in one column and join them in a comma-separated list. I am already in a stored procedure, so I can do it some way that way; however, I am curious if I can do this with a query. I am on SQL Server 2008. This query gets me the values I want:

SELECT DISTINCT faultType FROM @simFaults;

Is there a way (using CONCAT or something like that) where I can get the list as a single comma-separated value?

This worked for me on a test dataset.

DECLARE @MyCSV Varchar(200) = ''

SELECT @MyCSV = @MyCSV +
CAST(faulttype AS Varchar) + ','
FROM @Simfaults
GROUP BY faultType

SET @MyCSV = LEFT(@MyCSV, LEN(@MyCSV) - 1)

SELECT @MyCSV

The last part is needed to trim the trailing comma.

+1 to JNK - the other common way you will see, which doesn't require a variable is:

SELECT DISTINCT faulttype + ','
FROM @simfaults
FOR XML PATH ('')

Note that if faulttype contains characters like "<" for example, those will be xml encoded. But for simple values this will be OK.

this is how we do this

create table #test (item int)
insert into #test 
values(1),(2),(3)

select STUFF((SELECT  ', ' + cast(Item as nvarchar) 
        FROM #test 
        FOR XML PATH('')), 1, 2, '')

Without the space after the comma it would be;

select STUFF((SELECT  ',' + cast(Item as nvarchar) 
        FROM #test 
        FOR XML PATH('')), 1,1, '')