How to get Comma seperated list of values in SQL Server

Description:

                In SQL Server, we could query table column values into comma separated list. Here I could found two efficient ways to do the same. The first will be used mostly in procedures as it requires temporary variable, while other could be used in single select query with the help of stuff() function and XML FOR PATH() methods.

Data:

We will be having following data to execute Queries.

Employee Table

CREATE TABLE [dbo].[employee](

      [empid] [int] NOT NULL,

      [name] [varchar](100) NULL,

      [salary] [decimal](18, 0) NULL,

      [deptid] [int] NULL)

Department Table

CREATE TABLE [dbo].[department](

      [deptid] [int] NOT NULL,

      [deptname] [varchar](100) NULL)

Method 1:

Query:

DECLARE @list VARCHAR(MAX)

SELECT @list = COALESCE(@list + ',' , '') + name FROM employee

SELECT @list 'Comma Seperated List'

Result:

Comma Seperated List

-----------------------------------------------------------------------

Allyn,Bailey,Becky,Calista,Elisa,Franklyn,Hervey,Jackson,Kimberly,Lecia

(1 row(s) affected)

Method 2:

Query:

SELECT STUFF

((SELECT ',' + NAME

FROM employee

FOR XML PATH(''))

,1,1,'') 'Comma Seperated List'

Result:

Comma Seperated List

-----------------------------------------------------------------------
Allyn,Bailey,Becky,Calista,Elisa,Franklyn,Hervey,Jackson,Kimberly,Lecia

(1 row(s) affected)

Example:

                In example we will be showing employee working in particular department as comma separated values.

Query:

SELECT T1.deptname 'Department Name',

STUFF

((SELECT ',' + name FROM employee T2 WHERE T2.deptid = T1.deptid

FOR XML PATH('')),1,1,'') Employees

FROM

department T1

Result:

deptname                                           Employees

----------------------------------------------------------------------------------------------------

Administration                                            Allyn,Elisa

Marketing                                                  Bailey,Kimberly

Shipping                                                     Jackson

Information Technology                              Becky,Hervey,Lecia

Sales                                                          Calista

Accounting                                                 Franklyn

(6 row(s) affected)