Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Mar 18, 2001

Case Statement Tricks

By Neil Boyle

The CASE statement is a very flexible tool. Here are just a few of the tricks you can work with it.

Item translation

One of the simplest things you can do is to derive a columns contents based on it's contents, or the contents of another data item.

Click here for code example 1.

Titling subtotals

You can use CUBE and ROLLUP to produce totals and subtotals within a result set, but because SQL Server places NULLS for non-totaled columns in the total rows, they do not always look very pretty. In this example we use the CASE statement to place emphasis on the subtotal lines.

Click here for code example 2.

Simplifying Output

This batch lists of sales by store, and gives a general indication of the stores performance against the average quantity of sales per store.

Click here for code example 3.

This query batch starts by calculating the average of total sales per store, which is stored in the variable @storeAvg, then that average is used to rate the stores sales performance.

Grouping data into ranges

This example calculates the number of orders in certain size ranges. It also shows off the power of SQL as a set-based language: to code this as in a procedural language like VB would not be able to do this in a single statement.

Click here for code example 4.

Complex Sequencing

CASE statements can be used in an 'ORDER BY clause to order non-sequential data. This example sorts stores by name, but puts all the stores in California at the top of the list.

SELECT stor_name, state
FROM   stores
ORDER  BY CASE state
          WHEN 'CA' then 1
          ELSE 2
       END,
       stor_name

Further reading

This article discusses how to increase performance of complex UPDATE statements by using CASE.

This SQL 2000 article contains a section that shows how to use the CASE statement in a User Defined Function to enforce complex declarative constraints



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM