What is the syntax of using Case when in SQL?

Question

case when

Sachin Singh · Answer

it is similar to if-else, it ends with end keyword but doesn't start with begin. select case when age 'Todller' when age>5 and age 'Kids' when age>15 and age 'Teen' when age>18 and age 'Adult' else 'Senior Citizen' end as AgeGroup from tblPerson

Muhammad Imran Ansari · Answer

The syntex for CASE-WHEN in SQL is as follow: SELECT CASE WHEN condition THEN Result ELSE Result END ; Here is an example of CASE statement in SQL. You have a table of employees and want to check the designation on the basis of salary, you can write your using CASE-WHEN as: Select EmployeeName, Salary, CASE WHEN Salary >=80000 AND Salary THEN 'Director' WHEN Salary >=50000 AND Salary THEN 'Senior Consultant' Else 'Consultant' END AS Designation from Employee Else is used when no case will be matched.

Uttam Kumar · Answer

Here is the syntax CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END ;

Anupam Maiti · Answer

Simple CASE expression: SELECT [BusinessEntityID] ,[MaritalStatus] ,MaritalStatusDesc = CASE WHEN [MaritalStatus] = 'S' THEN 'Single' ELSE 'Married' END FROM [AdventureWorks2017].[HumanResources].[Employee]; Nesting CASE expression: SELECT [BusinessEntityID] ,[JobTitle] ,[HireDate] ,Seniority = CASE WHEN DATEDIFF( YEAR ,[HireDate],GETDATE()) > 10 THEN 'Longer than 10 years' WHEN DATEDIFF( YEAR ,[HireDate],GETDATE()) = 10 THEN 'Exactly 10 years' WHEN DATEDIFF( YEAR ,[HireDate],GETDATE()) THEN 'Shorter than 10 years' ELSE 'N/A' END FROM [HumanResources].[Employee]; NOTE : CASE can not only be used in SELECT statements, but at any other place where an expression can be written. This includes UPDATE, DELETE, SET, MERGE, a WHERE or HAVING clause and an ORDER BY clause. Example use in Order By : SELECT [BusinessEntityID] ,[JobTitle] FROM [AdventureWorks2017].[HumanResources].[Employee] ORDER BY CASE WHEN [JobTitle] LIKE 'Chief%' THEN 1 WHEN [JobTitle] LIKE 'Vice%' THEN 2 WHEN [JobTitle] LIKE '%Senior%' THEN 3 WHEN [JobTitle] LIKE '%Manager%' THEN 4 ELSE 5 END , [JobTitle]

Kapil Singh Kumawat · Answer

Hello, Here is the sample example of using case in sql server --Simple CASE expression: CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END for more info please refer Microsoft Docs- https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver15

Md Sarfaraj · Answer

Hello Bob, Check out the below links maybe it will help you. https://www.w3schools.com/sql/sql_case.asp https://www.freecodecamp.org/news/case-statement-in-sql-example-query/

Ravinder Sharma · Answer

CASE statements are used to create different outputs (Usually in the SELECT statement). It is SQL's way of handling if-then logic. SELECT CASE WHEN [CONDITION 1] THEN 'OUTPUT 1' WHEN [CONDITION 2] THEN 'OUTPUT 2' ELSE 'OUTPUT 3' END FROM [TABLE_NAME] In the above query, the output will be based on the conditions, if CONDITION 1 is true then the output will be OUTPUT 1, likewise, if CONDITION 2 is true then the output will be OUTPUT 2, if both conditions are false then the output will be OUTPUT 3.

What is the syntax of using Case when in SQL?

Answers (7)