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)

SQL Server - Stuff()

Define:

             Stuff is a TSQL function which is use to delete specified length of Character with in a string and replace with another set of characters.

Syntax:

STUFF(Character_Expression, Start, Length, replaceWith_expresstion)

Character_Expression:

              It is an expression of character data, which can be constant, variable or table column.

Start :

             This indicates the starting position of the character in character_expression.

Length:

              It is the length of characters which need to be replaced.

replaceWith_expresstion:

             It is the string that will be replaced from the start position to the length position.

Return Type:

             Return type will be same as of Character_Expression.

Example:

     SELECT STUFF('Have a good day',8,4,'nice');

Result:

             Have a nice day