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