SQL CHOOSE

SQL Server has a list of logical functions that can be used on data.  The first of these logical functions is the CHOOSE() function.  This function will return the value identified at the specified index.  Here is what the syntax for the CHOOSE() function looks like:

CHOOSE(index value, value 1, value 2, value 3, etc.)

A pretty simple example of the CHOOSE() function looks like this:

SELECT CHOOSE(3, 'Executive', 'Senior Management', 'Vice President')

The above would return 'Vice President', since the index for lookup is 3 and the third value is 'Vice President'.

So how would a function like this be useful?  Well, we could use it to list out the months of the year for an employee's birthday, like so:

SELECT CHOOSE(Month(Birthday), 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December')
FROM Employee

How does this differ from what you might see using the CASE() function?  There isn't a difference.  CHOOSE() is just a short hand version of CASE().  It definitely looks neater than CASE(), since you don't have to do so much coding.  Using the example above, CASE() would look something like this:

SELECT CASE Month(Birthday)
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
FROM Employee

As you can see, much mode coding needs to be done, while also adding the value you are looking for.  CHOOSE() already knows the value you are looking for and selects it based on the order in the provided value list.  Any value searched for that is not contained in the value list (such as 13 for our months) will return a NULL value.

How do you think you could use the CHOOSE() function at your organization?

To read up more on SQL CHOOSE(), go here: SQL CHOOSE

Using CASE Statements in Your Select

Let's say that you have a table of data that you want to group together.  Your data might be one of the following:

• You want to give a category or title to a list of ranges (using int, numeric, float, etc.)
• You want to group values that aren't necessarily in the same range (varchar, etc)
• You want to give more description to a single value or list of values
• You rely on more than one condition to fall under a particular category

There are a few ways that you could approach grabbing this data.  You could write separate queries, just grabbing the data you want grouped together, and then tying all data together using UNION ALL.  That query would look something like this:

SELECT 'Less than $10' as Category, count(*)

from Sales

where SalesAmount < 10

UNION ALL

SELECT 'Between $10 and $50' as Category, count(*)

from Sales

where SalesAmount between 10 and 50

UNION ALL

SELECT 'Over $50' as Category, count(*)

from Sales

where SalesAmount > 50

This certainly gets the job done, but leaves too much room for error.  Your other choice is to write a CASE statement in your select statement (which would need to be accompanied by using the same CASE statement in a group by).  Accomplishing the same output, you could do something like this:

SELECT Category = CASE

WHEN SalesAmount < 10 then 'Less Than $10'

WHEN SalesAmount between 10 and 50 then 'Between $10 and $50'

WHEN SalesAmount > 50 then 'Over $50'

END,

count(*)

from Sales
group by CASE
WHEN SalesAmount < 10 then 'Less Than $10'
WHEN SalesAmount between 10 and 50 then 'Between $10 and $50'

WHEN SalesAmount > 50 then 'Over $50'

END

You can also use the CASE statement to provide more than one condition:

SELECT Category = CASE
WHEN SalesAmount < 9 or SalesAmount > 99 then 'Small and Big Sales'
WHEN SalesAmount between 10 and 50 then 'Middle Sales'
END,
count(*)
from Sales
group by CASE
WHEN SalesAmount < 9 or SalesAmount > 99 then 'Small and Big Sales'
WHEN SalesAmount between 10 and 50 then 'Middle Sales'
END

Go here to read more on SQL CASE statements:

SQL CASE