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.