Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Saturday, September 17, 2011

Add / REMOVE Primary Key to existing table


CREATE TABLE WITHOUT PRIMARY KEY

CREATE TABLE JSSTUDTABLE (SNO NUMERIC(10) NOT NULL)

ADD PRIMARY KEY EXISTING TABLE

ALTER TABLE JSSTUDTABLE ADD  PRIMARY KEY (SNO);

REMOVE PRIMARY KEY EXISTING TABLE 

ALTER TABLE JSSTUDTABLE DROP  constraint  PK__JSSTUDTA__CA1EE06C63D8CE75

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