In this video we’ll walk you though how to use the CHOOSE function with SELECT. You’ll see how the function works and then a practical example using some sample data.
Once you’ve gone through this article, I would recommend watching our next Essential SQL Minute to continue learn more about SQL Server!
Once you have watched the video check out the sample code below. I’ve also included a transcript for you to use.
How to use the CHOOSE Function with SELECT
Transcript:
Welcome to another essential SQL minute. In today’s lesson, we’re gonna learn how to use the CHOOSE function with SELECT.
NOTE: There are more logical functions, such as CHOOSE, I would encourage you to go check them out.
The format for the CHOOSE function is CHOOSE and then an index and a value list, so let’s take a look at this a little closer and talk about the parameters.
CHOOSE(index, value list)
First let’s talk about what a value list is. Essentially a value list is just a list of literal values, typically tax values that are separated by commas, so a good example of value lists would be something like gold, silver, or bronze.
Read More: Introduction to SQL Server’s Built-In Logical Functions >>
The index in the CHOOSE function can be a number like one, two, or three and then we can use that index to select the item and the value list that we wish to display, so in our example here if I did, let’s say select CHOOSE two gold, silver, bronze, it’s gonna select the second item out of our value list, so this will choose silver.
SELECT CHOOSE(2,'Gold','Silver','Bronze')
Let’s run this and you’ll see that Silver was selected.
You may be wondering, well what happens if I decide to select zero, will it pick null or perhaps gold?
SELECT CHOOSE(0,’Gold’,’Silver’,’Bronze’)
Well let’s see. It picks NULL.
When I select something that is outside of the range, it picks null, so let’s try something that’s outside and above the range, such as four. Whoops. Hit execute on that and here it selects null again.
If it can’t find the index in the list, it’ll come back with null which is kinda cool. That’s a lot better than I think coming back with like an error, like index out of range, so I like that.
One thing that is interesting though is that if you do a CHOOSE of like CHOOSE two point five, and execute it, it will take the two point five and convert it to an integer and then run the command, so in this case it took two point five, changed it to the integer two, ran the command and then picked silver.
If this was, let’s say, two point six, it should still pick silver because it’s rounding it down, right? It’s basically, when it takes the integer it just lops off the decimal. It doesn’t care.
Alright, so let’s try a more comprehensive example using a query with a table from the AdventureWorks database.
Before we do that I want you to get the lay of the land of some data, so here is a query that I have set up that will select distinct job titles in organizations from the human resources employee table.
The reason I’m doing this is I want you to see the various organization levels that are in the database for employees and you can see here that, for instance, as my organization level is smaller, I start getting into the C level officers such as the chief financial officers and CEO’s and there’s the information services manager.
SELECT Distinct JobTitle,
OrganizationLevel
FROM HumanResources.Employee
As the organization level is a greater number, I start getting into individual contributor, those folks that are working on the production floor or in the offices, so I can use this information to … instead of outputting a number, for the organization level, perhaps giving a verbal description, and using the CHOOSE command select what that verbal description should be based off the organizational level number.
I’ve written a command that I’ll show you and then I’ll explain to you how it works.
Let me grab my command that I’ve got here.
SELECT NationalIDNumber, JobTitle, OrganizationLevel, CHOOSE(OrganizationLevel+1, 'Executive', 'Executive', 'Upper', 'Middle', 'Lower', 'Lower') as [Organization Level Name] FROM HumanResources.Employee
We are going to display the employee’s national ID number, their job title, their organization level, and then, based on that level, we are going to select whether they are an executive or upper, middle, or lower management.
Let’s run this and see how it goes. You can see now, when I run the command, I’m getting vice president of engineering, organization level one is an executive, so I took the org level of one, I added one to it, so org level plus one is two, so one two is an executive.
Upper is three, right? Cause there’s org level one or two, I’m sorry, so two plus one is three.
One, two, three is upper and so on. Someone in here I suppose is an organization level zero and that’s why I added the organizational level of plus one.
Here’s an example of how you can use CHOOSE with some real data to, in a sense, add some descriptive values to what would be otherwise numeric information.
Leave a Reply