“ COALESCE “ Method in Sql Server

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

CREATE TABLE devenvexe(Name nvarchar(10),homeaddress nvarchar(10),officeaddress nvarchar(10), Temp_addressnvarchar(10))
SELECT name,COALESCE(homeaddree,officeaddress,temp_address) Addreess FROM devenvexe

Output:

Name	Address
Sutahhar	Pudukkottai
Suresh	Pullanviduthi
Sumathi	Alangudi
Sujatha	Pullanviduthi

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