Search content within the blog

Thursday, June 4, 2009

Use of Coalesce function in SQL Server

The basic syntax of the coalesce function is a s follows...

select ( exp1, exp2,,,expn)as someresult from table

The function selects the first not null value of the expressions specified.

say you have a table called employee and his salary based on his job type can be inserted in either of the 3 columns stored ina table in sqlserver . Say the columns are col1, col2, col3....so st select his/her salary the statement with the help of coalesce would be as follows...
select coalesce(col1,col2,col3)* 5 as salary from dbo.tblSalary

An example shall make it more clear...

Create the following table in your database
/****** Object: Table [dbo].[tbCoalesce] Script Date: 06/04/2009 20:33:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tbCoalesce](
[col1] [int] NULL,
[col2] [int] NULL,
[col3] [int] NULL
) ON [PRIMARY]

GO


Also insert values using these statements...
insert into dbo.tbCoalesce (col1,col2,col3)
select null, null, 1 union
select null, 2, null union
select null, null, 3

paste the following query in quey analzer to see the output shown below

select coalesce(col1,col2,col3)* 5 as salary from
dbo.tbCoalesce

Salary
   5
   15
   10

You can also use it to pivot the resultset....

Using Coalesce to Pivot

If you run the following statement against the AdventureWorks database
SELECT Name
FROM HumanResources.Department
WHERE (GroupName = 'Executive General and Administration')


you will come up with a standard result set such as this.





If you want to pivot the data you could run the following command.
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.

No comments:

Post a Comment