“ COALESCE “ Method in Sql Server

 “ 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 display available first record means you can use coalesce method

CREATE TABLE devenvexe(Name  nvarchar(10),homeaddress nvarchar(10),officeaddress nvarchar(10), Temp_addressnvarchar(10))

Query :

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

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