Explore the different uses of case in SQL Server

The server case  may be one of the most misused keywords in  SQL . Although you may have used this keyword before to create a field, it has more usage. For example, you can use  case in the  WHERE  clause. First let's look at the syntax of  CASE . In general  SELECT , its syntax 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 contents of the angle brackets with specific parameters. The following is a simple example: 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 ' &NBSP;&NBSP;&NBsp; end From titles Order by price go  This is a typical use of  CASE , but using  CASE  Can actually do more things. For example, the  CASE:SELECT  ' Number of titles ' in the  GROUP BY  clause below,  count (*) from  titles Group by     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 and add a  ORDER BY  clause 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 BY      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 BY      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, in order to use in the  GROUP BY  block  case, the query statement needs to repeat the  CASE  block in the  GROUP BY  block in the  SELECT  block. In addition to choosing a custom field,  CASE  is useful in many cases. In a further step, you can also get a set of sorted results that you previously thought would be impossible. Turn from: http://www.g22.net/Article/Show.asp?ID=688