Friday, July 6, 2012

SQL SERVER – Use of COALESCE Function with Useful Example


Question: What is COALESCE Function?
Question: What is the use of COALESCE Function?
Question: How to get Vertical Row Values in a Single Row?
Question: How to Customize the Multiple Row values in Single Row?

It is one of the System defined function of SQL Server which used to customize the Multiple Row values in Single Row (Vertical to Horizontal). The following example runs on SSMS.

-- Select Database
Use AdventureWorks
GO

--Get Table value in general way

Select Title as TitleOfEmployees
From HumanResources.Employee
Where EmployeeID between 1 and 5

--Result of above query:

TitleOfEmployees
------------------------------------------------------------------
Production Technician - WC60
------------------------------------
Marketing Assistant
------------------------------------
Engineering Manager
------------------------------------
Senior Tool Designer
------------------------------------
Tool Designer

(5 row(s) affected)


--Use COALESCE Fuction

DECLARE @TitleOfEmployee VARCHAR(200)
Select @TitleOfEmployee= COALESCE(@TitleOfEmployee + ', ', '') + Title From HumanResources.Employee
Where EmployeeID between 1 and 5
Select @TitleOfEmployee as TitleOfEmployees

--Result of above query:

TitleOfEmployees
-----------------------------------------------------------------------
Production Technician - WC60, Marketing Assistant, Engineering Manager, Senior Tool Designer, Tool Designer

(1 row(s) affected)


If you have any other method so please let me know. Thanks.

Like and Share to SQL Integrity Blog

No comments:

Post a Comment

Thank You !!!!