Converting multiple rows of a single column into single row

COALESCE (Transact-SQL):-

 MSDN definition:- Returns the first nonnull expression among its arguments.

 Syntax

COALESCE ( expression [ ,...n ] )

ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL.

In today's example, we will be finding a way to convert mutliple rows of a single column into a delimited string value.

For example my sample table (tblEmployee) data in SQL Server is as follows (in tabular format):-

ID	Name	Department	Age
1	John Smith	Software	32
2	Steve Smith	Finance	35
3	Pradeep Pavaluru	Marketing Division	29
4	John Leo	Software	40

I want to combine all the names for the given employee records as a delimited string. For achieving the same i will be using COALESCE function.

declare @ReturnName varchar(max)

Select@ReturnName= COALESCE(@ReturnName + ',', '')+[Employee Name]

fromtblEmployee

print 'Employees Name= '''+@ReturnName+''''

Output:

Employees Name= 'John Smith,Steve Smith,Pradeep Pavaluru,John Leo'

Happy Coding.....