Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Thursday, September 8, 2011

“ 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