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)
1.
select EmpID,SUM(isnull(SalaryType1,0))+SUM(isnull(SalaryType2,0))+SUM(isnull(SalaryType3,0)) from EmpSalary
group by EmpID
2.
select EmpID,sum(Coalesce(SalaryType1,SalaryType2,SalaryType3)) from EmpSalary
group by EmpID
3.
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=>
1,2,3,4,5,6