SQL – Using COALESCE to Build Comma-Delimited String

I had a query that returned one column with multiple records.  I wanted only one record with all the values to be comma separated.  I could do this with a cursor, but that throws a big warning sign.  So i decided to use the COALESCE function.  Below are some of the resources i used to solve my problem.
 
 

–**************************************************************************
–http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

DECLARE

@EmployeeList varchar(100)

SELECT

@EmployeeList = COALESCE(@EmployeeList + ‘, ‘, ) +

CAST(Emp_UniqueID AS varchar(5))

FROM

SalesCallsEmployees

WHERE

SalCal_UniqueID = 1

SELECT

@EmployeeList

–**************************************************************************
–http://www.sqlservercentral.com/scripts/Miscellaneous/31922/

create

procedure sp_return_students

as
set

nocount off

/* Declare variable which will store all student name */

Declare

@StudentName varchar(8000)

/* Query that will return student names and at the same time concatenate values. */

select

@StudentName = coalesce(@StudentName + ‘, ‘, ) + stu_name from tbl_students

/* At last, you just have to define column name that will store values */

Select

@StudentName As Student

Leave a Reply

Your email address will not be published. Required fields are marked *