{"id":25958,"date":"2020-10-23T19:03:23","date_gmt":"2020-10-23T19:03:23","guid":{"rendered":"https:\/\/ittutorial.org\/?p=25958"},"modified":"2020-10-23T19:23:54","modified_gmt":"2020-10-23T19:23:54","slug":"sql-case-statement","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/sql-case-statement\/","title":{"rendered":"SQL CASE Statement"},"content":{"rendered":"

SQL CASE statement is very powerful and versatile. It can be used in SELECT, UPDATE and INSERT statement. Even it can be used in ORDER BY and GROUP BY clause. Let’s examine them one by one.<\/p>\n

Syntax<\/strong><\/h2>\n

First check out the syntax of CASE statement:<\/p>\n

CASE\r\nWHEN\u00a0condition1\u00a0THEN\u00a0result1\r\nWHEN\u00a0condition2\u00a0THEN\u00a0result2\r\n\r\n...\r\nWHEN\u00a0conditionN\u00a0THEN\u00a0resultN\r\nELSE\u00a0result\r\nEND;\r\n<\/pre>\n
Data Preparation<\/h2>\n
First chose your preferred database and run below queries for creating a table “Persons” and populating with data.<\/p>\n
IF OBJECT_ID('Persons', 'U') IS NOT NULL\r\nDROP TABLE Persons;\r\nGO\r\n\r\nCREATE TABLE Persons(\r\nname varchar(20)\r\n,age int\r\n,sex varchar(2)\r\n)\r\n\r\nGO\r\n\r\nINSERT INTO Persons (name,age,sex) values('Bob',5, 'M');\r\nINSERT INTO Persons (name,age,sex) values('Harry',15, 'M');\r\nINSERT INTO Persons (name,age,sex) values('Jasmine',25, 'F');\r\nINSERT INTO Persons (name,age,sex) values('Fanny',65, 'F');\r\nINSERT INTO Persons (name,age,sex) values('Evan',-1, 'N');\r\n\r\nGO<\/pre>\n
SELECT Statement<\/h2>\n
Let’s use it in our SELECT statement.<\/p>\n
SELECT Name, age as 'Actual Age',\r\nCASE\r\nWHEN age >= 0 AND age <= 12 THEN 'CHILD'\r\nWHEN age >= 13 AND age <= 19 THEN 'TEEN AGE'\r\nWHEN age >= 20 AND age <= 40 THEN 'YOUNG'\r\nWHEN age >= 41 AND age <= 60 THEN 'MIDDLE AGE'\r\nWHEN age >= 61 THEN 'OLD'\r\nELSE 'NOT DEFINED' END as 'New Age using CASE Stmt',\r\nsex as 'Actual Gender',\r\nCASE\r\nWHEN sex = 'M' THEN 'MALE'\r\nWHEN sex = 'F' THEN 'FEMALE'\r\nWHEN sex = 'N' THEN 'DONT WANT TO DISCLOSE'\r\nEND as\u00a0 'New Gender\u00a0 using CASE Stmt'\r\nFROM Persons<\/pre>\n
Output<\/strong><\/span><\/p>\n
\"\"<\/p>\n
INSERT Statement<\/strong><\/h2>\n
DECLARE @age INT\r\nDECLARE @sex VARCHAR(10)\r\n\r\nSET @age = 40\r\nSET @sex = 'MALE'\r\nINSERT INTO Persons (Name, Age, Sex)\r\nVALUES(\r\n'Jack',\r\nCASE WHEN @age < 0 THEN -1 ELSE @age END,\r\nCASE WHEN @sex = 'MALE' THEN 'M'\r\nWHEN @sex = 'FEMALE' THEN 'F'\r\nELSE 'N' END\r\n)<\/pre>\n
Output<\/span><\/strong><\/p>\n
If we run the our first SELECT statement again, then output would be:<\/p>\n
\"\"<\/p>\n
UPDATE Statement<\/strong><\/h2>\n
DECLARE @age INT\r\nSET @age = -4\r\n\r\nUPDATE Persons SET\r\nage = CASE WHEN @age < 0 THEN -1 ELSE @age END\r\nWHERE Name = 'Bob'<\/pre>\n
Output<\/strong><\/span><\/p>\n
If we run the above SELECT statement again, then output would be:<\/p>\n
\"\"<\/p>\n
ORDER BY Clause<\/strong><\/h2>\n
CASE can be added in ORDER BY clause in above SELECT statement\r\nSELECT Name, age as 'Actual Age',\r\nCASE\r\nWHEN age >= 0 AND age <= 12 THEN 'CHILD'\r\nWHEN age >= 13 AND age <= 19 THEN 'TEEN AGE'\r\nWHEN age >= 20 AND age <= 40 THEN 'YOUNG'\r\nWHEN age >= 41 AND age <= 60 THEN 'MIDDLE AGE'\r\nWHEN age >= 61 THEN 'OLD'\r\nELSE 'NOT DEFINED' END as 'New Age using CASE Stmt',\r\nsex as 'Actual Gender',\r\nCASE\r\nWHEN sex = 'M' THEN 'MALE'\r\nWHEN sex = 'F' THEN 'FEMALE'\r\nWHEN sex = 'N' THEN 'DONT WANT TO DISCLOSE'\r\nEND as\u00a0 'New Gender\u00a0 using CASE Stmt'\r\nFROM Persons\r\nORDER BY\r\nCASE WHEN sex='M' THEN age END,\r\nCASE WHEN sex='F' THEN Name END<\/pre>\n
Output<\/strong><\/span><\/p>\n
\"\"<\/p>\n
GROUP BY Clause<\/strong><\/h2>\n
Now time to explore CASE in GROUP BY clause in our first SELECT statement:<\/p>\n
SELECT\r\nCASE\r\nWHEN age <= 40 THEN 'YOUNG'\r\nWHEN age >= 41 THEN 'OLD'\r\nEND as 'New Age using CASE Stmt', COUNT(*)\r\nFROM Persons\r\nGROUP BY CASE\r\nWHEN age <= 40 THEN 'YOUNG'\r\nWHEN age >= 41 THEN 'OLD'\r\nEND<\/pre>\n
Output<\/strong><\/span><\/p>\n
\"\"<\/strong><\/p>\n
We covered all cases of our today’s CASE topic.<\/p>\n
Stay Home! Stay Safe!<\/p>\n
It was first published in my blog<\/a>.<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
SQL CASE statement is very powerful and versatile. It can be used in SELECT, UPDATE and INSERT statement. Even it can be used in ORDER BY and GROUP BY clause. Let’s examine them one by one. Syntax First check out the syntax of CASE statement: CASE WHEN\u00a0condition1\u00a0THEN\u00a0result1 WHEN\u00a0condition2\u00a0THEN\u00a0result2 … WHEN\u00a0conditionN\u00a0THEN\u00a0resultN ELSE\u00a0result END; Data Preparation First …<\/p>\n","protected":false},"author":10927,"featured_media":25973,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[2227],"tags":[15803,15802,15804],"class_list":["post-25958","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-sql-server-2","tag-case-statement","tag-sql-case-statement","tag-sql-server-case-statement"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/10\/case_stmt.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/25958","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/10927"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=25958"}],"version-history":[{"count":8,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/25958\/revisions"}],"predecessor-version":[{"id":25972,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/25958\/revisions\/25972"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/25973"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=25958"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=25958"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=25958"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}