Use of COALESCE

The purpose of COALESCE function in MS-SQL is to return nonnull expression within given arguments. In an example below the result will be the value that is not null and is found first within the given arguments (attributes in the table).

USE AdventureWorks ;
GO
SELECT Name, Class, Color, ProductNumber, COALESCE(Class, Color, ProductNumber) AS FirstNotNull
FROM Production.Product ;
GO
RESULTS:
If Class and Color Values are null and ProductNumber is '123' it will print '123' As FirstNotNull.

We can use Coalesce for Pivot also. Run the below command in Adventureworks database:

SELECT Name FROM HumanResources.Department WHERE (GroupName = 'Executive General and Administration')

Here we will get the standard Result like this:

Now if we want to Pivot the data, we can use like this:

DECLARE @DepartmentName VARCHAR(1000)

SELECT @DepartmentName = COALESCE(@DepartmentName,'') + Name + ';' 
FROM HumanResources.Department
WHERE (GroupName = 'Executive General and Administration')

SELECT @DepartmentName AS DepartmentNames

and get the following result set.