Comma Separated values in SQL Query

Question

I have a table called contacts and i want to select all the distinct contact names (int) in one line separated by comma.

Currently i am using:

SELECT DISTINCT name FROM contacts

To get this result:

11111
22222
33333

But i need to have this result:

11111, 22222, 33333

I saw some examples with XML PATH but i couldn't adapt it to my code. Can anybody help me with the query please?

this might be what you are looking for? http://blog.sqlauthority.com/2008/06/04/sql-server-create-a-comma-delimited-list-using-select-clause-from-table-column/ — trainoasis, Jun 11 '14 at 09:54

score 2 · Accepted Answer · answered Jun 11 '14 at 09:55

2

STUFF + XML PATH will do the job:

SELECT STUFF(
             (SELECT Distinct ',' + cast (name as varchar(20)) 
              FROM contacts       
              FOR XML PATH (''))
             , 1, 1, '')

demo:http://sqlfiddle.com/#!3/748e4/1

answered Jun 11 '14 at 09:55

Milen

8,697
7
43
57

so your name column is `int` - added cast as varchar.. – Milen Jun 11 '14 at 10:01

score 0 · Answer 2 · answered Jun 11 '14 at 09:57

0

You can use STUFF:

SELECT 
    STUFF((
      SELECT distinct ',' + c.contacts
      FROM dbo.contacts c
      FOR XML PATH('')), 1, 1, '') as names;

answered Jun 11 '14 at 09:57

Ivan Cachicatari

4,212
2
21
41

score 0 · Answer 3 · answered Jun 11 '14 at 10:02

       declare @t table  (Name Varchar(50))

       Insert into @t (Name) values ('1111'), ('2222'), ('3333')

        DECLARE @tt VARCHAR(MAX) 
        SELECT @tt= COALESCE(@tt + ', ', '') + Name from @t 
        SELECT @tt AS Result

OR 

   declare @t table  (Name Varchar(50))

   Insert into @t (Name) values ('1111'), ('2222'), ('3333')

SELECT SUBSTRING(
(SELECT ',' + s.Name
FROM @t s
ORDER BY s.Name
FOR XML PATH('')),2,200000) As Names
GO

score 0 · Answer 4 · answered Jun 11 '14 at 10:03

Try this:

declare @contacts table(name nvarchar(100))

INSERT INTO @contacts (name) VALUES
('John'),
('Andrew'),
('Paul')

DECLARE @Result AS NVARCHAR(1000) -- Or even MAX if you need

SELECT @Result = ISNULL(@Result + ', ', '') + C.name
FROM (SELECT DISTINCT name FROM @contacts) C

SELECT @Result -- This is your actual result

The only thing you need to watch for is that the DISTINCT may affect the order of the names. That may or may not matter to you.

Comma Separated values in SQL Query

4 Answers4