COALESCE Function In Sql Server

During Development we have use more time Colesce method If someone have home address or office address suppose if you dispaly available first record means you can use coalesce method or concatinate columns we use this coding

I have Below Table :

Insert Some Data in it :

Now the basic use of Colesce:
You can see the Id 2 hasnot OfficePhoneNo and id 5 has only MobileNo.
Colesce function can display available first record from one of the column.

It's Query Like :

select name ,coalesce(OfficePhoneNo,HomePhoneNo,MobileNo)
PhoneNo from EmpDetails

and Result will be :

Now I can show you another example of it.

We can also use coalesce function for Combining all column into single column.

CREATE TABLE Emp(FName VARCHAR(25))

INSERT INTO Emp VALUES('PalPatel')
INSERT INTO Emp VALUES('DishaAgola')
INSERT INTO Emp VALUES('Bansi')
INSERT INTO Emp VALUES('Nirav')


DECLARE @nam NVARCHAR(1024)

SELECT @nam=COALESCE(@nam+',', '')+ FName FROM Emp

SELECT Emp= @nam

Output :

PalPatel,DishaAgola,Bansi,Nirav

Thats it .....