“ 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