“ COALESCE “ Method 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. Learn COALESCE Method in Sql Server.

About COALESCE Method in Sql Server


Table Have so many column like that

Name HomeAddress OfficeAddress Temp_Address
Suthahar Null Null Pudukkottai
Suresh Pullanviduthi Null Null
Sumathi Null Alangudi Null
Sujatha Pullanviduthi Pudukkottai Trichy

If someone have home address or office address suppose if you dispaly available first record means you can use coalesce method

CREATE TABLE JS_SQUARE(Name nvarchar(10),homeaddress nvarchar(10),officeaddress nvarchar(10), Temp_address nvarchar(10))


Query :


SELECT name,COALESCE(homeaddree,officeaddress,temp_address) Addreess FROM JS_SQUARE


Output:
Name Address
Sutahhar Pudukkottai
Suresh Pullanviduthi
Sumathi Alangudi
Sujatha Pullanviduthi


Concatinate Column in Single Column


CREATE TABLE JS(SNAME NVARCHAR(10))

INSERT INTO JS VALUES('SUTHAHAR')
INSERT INTO JS VALUES('SURESH')
INSERT INTO JS VALUES('SUMATHI')
INSERT INTO JS VALUES('SUJATHA')


DECLARE @VAL NVARCHAR(1024)

SELECT @VAL=COALESCE(@VAL+',', '')+ SNAME FROM JS

SELECT JS= @VAL


Output:
Suthahar,Suresh,Sumathi,Sujatha


Comments

Author: CHAITANYAA VARMA24 Sep 2011 Member Level: Bronze   Points : 1

Hi Buddy ,

I'm completely new to this site.
what u specified is grt and 1 more add-on on the same the scenario what you explained will occur mostly in reporting stuff.

Eg.,SCenario:
Business Req: For a product company there will be agentgroups and for each agentgroup there will be agents. The requirement The user needs a agentgroup info in the Report header and all the agents info belonging for a group.
So as a developer i will retrieve all the agents info in columns for a particular group but while presenting thru a SP we need go with a comma seperated value so that why COALESCE is used for combining all the agents with a COMMA SEPERATED. Thats why its called a CSV(Comma Seperated Values).

Cheers,
ChaitanyaaVarma



  • 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:
    Email: