Tuesday, July 22, 2014

Coalesce function in sql server 2008



Coalesce(Add together different elements) used as a Case statement in SQl server.If the value in one of the columns is not available then use the value of the second column and if that is also not available, then use the third column. If none of the columns have the value, then null is returned for that value.


I’ve a table having structure as depicted below:




I will apply on Coalesce function on Category table to fetch information of three columns in a single column alias.it ignores null values of each columns.

I have created a query to retrieve desired information.kindly see an image depicted below:



if you notice in above shown image we have combined three columns(having null values also) together and get as single column Category.


Note: it doesn’t fit in that condition, if you have columns having values (other than Null) in a table and want to put into Coalesce function .

I’ve changed a structure little bit and added one more column Technology into Category table.Kindly see a Category table structure in an image shown below.





It returns value of first non-null column.I’ve changed a query as well and added “Technology” column Coalesce function.

Kindly refer image shown below.




Its very simple to learn and may be utilized in future.


Sachin Kalia


Post a Comment