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

SQL Server – Avg()

Define:

             Avg() is an Aggregate function which is use to get average of the numbers of columns.

Syntax:

AVG([All | Distinct]numeric_expression)

All:

             By default AVG(average) function applies to all.

Distinct:

             Specifies that AVG(average) is performed on each unique instance of a value, regardless how many times value had occurred.

Numeric_Expression:

              It is a numeric expression on which average to be performed. It could be column with numeric values or any unit which can be measured.

Return Types:

             Int, BigInt, Decimal, Money, Float.                                                                       

Example Table:

Employee Table:

empid       name                                salary

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

1001        Allyn                                10000 

1002        Bailey                               14000 

1003        Becky                              12000 

1004        Calista                              12000 

1005        Elisa                                 14000 

1006        Franklyn                           12000 

1007        Hervey                             14000 

1008        Jackson                            15000 

1009        Kimberly                          18000 

1010        Lecia                                16000 

(10 row(s) affected)

Example: All

             Suppose we have table employee, which have following data and we need average of salary of all employees.

     SELECT AVG(SALARY) AS 'Average Salary' FROM EMP;

    

Result:

Average Salary

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

13700.000000

(1 row(s) affected)

Example: Distinct

             If we need average salary for all employee who have distinct salary.

             SELECT AVG(DISTINCT SALARY) AS 'Average Salary' FROM EMP;

Result:

Average Salary

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

14166.666666

(1 row(s) affected)