How to use Coalesce in Sql server

In this article I will be explaining you about Coalesce Function in MS SQL server. The Coalesce function is one of the very useful function and using that function we can reduce the lot of ode in Sql. In this Article we will see how to use Coalesce function in MS SQL with a sample query.

Coalesce function in MS SQL can be separated into three parts.
1. In first Query define that column has null value or not after that use sum function.
2. You are seeing in second query only use coalesce function after that use Sum function. It reduces the code.
3. In third Query It concatenate ID with the Help of Coalesce Function to which you can see.

create table EmpSalary(ID int identity,
EmpID int,SalaryType1 Decimal(12,2),
SalaryType2 Decimal(12,2),
SalaryType3 decimal(12,2),
sMonth int,sYear int)

insert into EmpSalary values(101,1200,null,null,1,2012)
insert into EmpSalary values(102,1250,null,null,1,2012)
insert into EmpSalary values(101,null,12500,null,2,2012)
insert into EmpSalary values(102,null,11000,null,2,2012)
insert into EmpSalary values(101,null,null,25000,2,2012)
insert into EmpSalary values(102,null,null,25000,2,2012)


select EmpID,SUM(isnull(SalaryType1,0))+SUM(isnull(SalaryType2,0))+SUM(isnull(SalaryType3,0)) from EmpSalary
group by EmpID


select EmpID,sum(Coalesce(SalaryType1,SalaryType2,SalaryType3)) from EmpSalary
group by EmpID


declare @coalese varchar(500)
select @coalese=coalesce(@coalese+','+CAST(ID As varchar),CAST(ID As varchar)) from EmpSalary
print @coalese

First Query OutPut=>
EmpID Salary
101 38700.00
102 37250.00

Second Query Output=>
EmpID Salary
101 38700.00
102 37250.00

Third Query Output=>


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: