Saturday, December 17, 2011

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.....



No comments :

Post a Comment