Search Web

Tuesday, October 11, 2011

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 .....


Parthiv Agola - Find me on Bloggers.com

7 comments:

obat mata minus said...

postingan yang bagus tentang"COALESCE Function In Sql Server"

Anonymous said...

Pretty! This was an extremely wonderful article. Many thanks for supplying these details.


my web page street legal golf carts arizona

Anonymous said...

certainly like your website but you need to take a look at the
spelling on quite a few of your posts. Several of them are rife with spelling issues and I in finding it very troublesome to inform the truth then again I will certainly
come again again.

Here is my page - discount Golf clubs used

Anonymous said...

These are genuinely enormous ideas in about blogging.
You have touched some pleasant things here. Any way keep up wrinting.



Review my weblog championgolfgifts.com

Anonymous said...

Hiya! I just want to give a huge thumbs up for the
great info you may have here on this post. I will probably be coming back to your weblog for extra soon.


My homepage ... travel alarm clock radio shack

Anonymous said...

Hey! I simply wish to give an enormous thumbs up for the
great data you’ve got right here on this post.

I might be coming back to your blog for more soon.

My web-site ... rfd country music tv schedule

Anonymous said...

Hello! I simply wish to give an enormous thumbs up for the nice info you’ve gotten here on this post.
I might be coming again to your weblog for more soon.


Review my homepage :: html href code for image

Post a Comment