Swim in SQL server

Case Statement in SQL Srever

How to use Case Statement in SQL Server? Here i explained Case statement with simple exampleS.

CREATE TABLE #Student(Stud_ID INT IDENTITY, NAME VARCHAR(100), DeptID INT, DOB DATETIME,Gender CHAR(1),JoiningDate Datetime,Marks INT)

INSERT INTO #Student

VALUES (‘ABHISHEK’ , 1 , ’01-JAN-1986′,1, ’01-JAN-2011′,50),

(‘PRASHANTH’, 1 ,’01-JUN-1985′,1,’01-JUN-2010′,70),

(‘RAMS’, 2 ,’01-MAR-1986′,1,’01-MAR-2011′,60),

(‘SRIRAM’, 3 ,’01-JAN-1985′,1 ,’01-JAN-2010′,50),

(‘RAM’, 1 ,’01-JAN-1986′,1,’01-JAN-2011′,20),

(‘ABHI’, 2 ,’01-JAN-1986′,1,’01-JAN-2011′,70),

(‘RANI’, 1 ,’01-JAN-1986′,2,’01-JAN-2010′,80),

(‘JYOTHI’,3 ,’01-JAN-1985′,2,’01-JAN-2011′,90),

(‘PRIYA’, 2 ,’01-JAN-1986′,2,’01-JAN-2011′,34),

(‘ROJA’, 1 ,’01-JAN-1985′,2,’01-JAN-2010′,50)

SELECT*FROM #Student

################# Example 1 #################

/*USING CASE STATMENT IN SELECT

HERE I WANT TO DISPLAY THE GENDER(M,F) ACCORDING TO GENDER COLUMN,IF GENDER=1 THEN M, GENDER= 2 THEN F

*/

SELECT S.Stud_ID , S.NAME , S.DeptID , S.DOB

,CASE S.Gender WHEN 1 THEN ‘M’

WHEN 2 THEN ‘F’

ELSE ‘UNKNOWN’

END AS Gender

,S.Marks,S.JoiningDate

FROM #Student S

/*

Here we are cheking condition based on only single Column(Gender) with equals operator.

So I have Written “S.Gender” After “CASE” Statment.

*/

/*

################# Example 2 #################

Here we can know how to use Conditional Operators in Case Statment.

Using Case Statment we can Give Grade of students.

*/

SELECT S.Stud_ID , S.NAME , S.DeptID , S.DOB

,CASE S.Gender WHEN 1 THEN ‘M’

WHEN 2 THEN ‘F’

ELSE ‘UNKNOWN’

END AS Gender

,S.Marks

,CASE WHEN S.Marks <= 34 THEN ‘FAIL’

WHEN S.Marks >= 35 AND S.Marks <= 59 THEN ‘3rd Class’

WHEN S.Marks >= 60 AND S.Marks <= 74 THEN ‘2nd Class’

WHEN S.Marks >= 75 AND S.Marks <= 100 THEN ‘1st Class’

END AS Grade

,S.JoiningDate

FROM #Student S

ORDER BY Grade , S.NAME

/*

Here am going to show how to use CASE Statment in WHERE Clause.

################ Example 3 ##########################################

*/

SELECT*

FROM #Student S

WHERE (CASE S.DeptID WHEN 1 THEN’CSE’

WHEN 2 THEN ‘CSIT’

WHEN 3 THEN ‘ECE’

WHEN 4 THEN ‘EIE’

END ) = ‘CSE’

/*

Here am going to show, How to use CASE Statment in ORDER BY Clause.

###################### Example 4 ####################

As per my requirement, The first record should be Fixed Value ,

Like In my below example,

If i write Order by Branch, then i will get the output order as CSE , CSIT , ECE .

If i choose Desc Order then output order is ECE,CSIT,CSE.

I want to display CSIT Records first and other branches in desc order.

*/

SELECT S.Stud_ID , NAME , CASE S.DeptID WHEN 1 THEN ‘CSE’

WHEN 2 THEN ‘CSIT’

WHEN 3 THEN ‘ECE’

WHEN 4 THEN ‘EIE’

END AS Branch

,S.Gender,S.JoiningDate

INTO #Tmp_Student

FROM #Student S

SELECT*

FROM #Tmp_Student

ORDER BY CASE Branch WHEN’CSIT’THEN 0 ELSE 1 END,Branch desc, NAME

/*Using with Case Statment , we can Generate CROSS TAB(Crystal Reports)/Matrix (SSRS) Type output.*/

SELECT Branch ,

COUNT(CASE WHEN YEAR(JoiningDate)=2010 THEN Stud_ID ELSE NULL END) AS [2010]

,COUNT(CASE WHEN YEAR(JoiningDate)=2011 THEN Stud_ID ELSE NULL END) AS [2011]

,COUNT(CASE WHEN YEAR(JoiningDate)=2012 THEN Stud_ID ELSE NULL END) AS [2012]

FROM #Tmp_Student

group by Branch

/*

We Can use Case statment in UPDATE Statment also.

Here is the Example.

*/

UPDATE TS SET TS.Gender =(CASE TS.Gender WHEN 1 THEN’M’

WHEN 2 THEN ‘F’

ELSE ‘UNKNOWN’

END)

FROM #Tmp_Student TS

SELECT*FROM #Tmp_Student

/* Clear Temp Memory */

DROPTABLE #Student , #Tmp_Student

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: