Using COALESCE function to concatenate column data in sql query

Published by waqas at 7:49 PM under SQL

May time we need column's data into a single row from a database table. This is easy to do if we are doing this at application level. However this could also be done in a sql query as well.

For example, suppose we have a table for "Employees":

SELECT * FROM Employees

EmpId      EmployeeName
-----------------------
1001         Susan
1002         Waqas
1003         Amjad
1004         Naveed
1005         David

Now I want to show all employee names in a single row separated by comma (,). One commonly used approach is to fetch all rows and concatenate data into out application

while (dr.Read())
{
  EmployeeNames += dr["EmployeeName"] + ",";
}

We can do the same thing in a Sql Server Query also

DECLARE @EmployeeName VARCHAR(8000) 
SELECT @EmployeeName = COALESCE(@EmployeeName + ', ', '') + EmployeeName FROM Employees
SELECT EmployeeName = @EmployeeName

EmployeeName
------------
Susan, Waqas, Amjad, Naveed, David

The COALESCE function is used to ensure that there is no comma (,) after the last EmployeeName.