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.
f8e7917a-6491-4819-b854-19c8ff92ae91|0|.0