Usage of case in SQL Server

Case may be one of the keywords most misused in SQL. Although this keyword may have been used before to create a field, it also has more features. For example, you can use case in the WHERE clause.

First, let's take a look at the case syntax. In a general SELECT statement, the syntax is as follows:

Reference content is as follows: Select <mycolumnspec> = Case When <A> then <somethinga> When <B> then <somethingb> Else <somethinge> End

In the above Code, you need to replace the content in angle brackets with specific parameters. The following is a simple example:

Reference content is as follows: Use pubs Go Select Title, 'Price range' = Case When price is null then 'unpriced' When price <10 then 'bargain' When price between 10 and 20 then 'average' Else 'Gift to impress relatives' End From titles Order by price Go

This is a typical example of case, but you can do more with case. For example, the case in the group by clause below:

Reference content is as follows: Select 'number of tidles ', count (*) From titles Group Case When price is null then 'unpriced' When price <10 then 'bargain' When price between 10 and 20 then 'average' Else 'Gift to impress relatives' End Go

You can even combine these options to add an order by clause, as shown below:

Reference content is as follows: Use pubs Go Select Case When price is null then 'unpriced' When price <10 then 'bargain' When price between 10 and 20 then 'average' Else 'Gift to impress relatives' End as range, Title From titles Group Case When price is null then 'unpriced' When price <10 then 'bargain' When price between 10 and 20 then 'average' Else 'Gift to impress relatives' End, Title Order Case When price is null then 'unpriced' When price <10 then 'bargain' When price between 10 and 20 then 'average' Else 'Gift to impress relatives' End, Title Go

Note: To use case in the group by block, the query statement must repeat the case block in the select block in the group by block.

In addition to selecting custom fields, case is useful in many cases. Further, you can get the grouping sorting result set that you previously thought was impossible to get.