What is CHOOSE?
CHOOSE
is certainly one of the less known functions in SQL Server, but this function is wrongly considered a shadowy existence, because it can make many annoying queries much easier. You can often use CHOOSE
to simplify long, complex CASE WHEN
statements, namely whenever you need to select an element from a list on the basis of a number, i.e. like an array access via an index.
How can you work with CHOOSE?
The basic functionality of CHOOSE
is quite simple: you pass a number (the index you want, which is 1-based here) as the first argument to the CHOOSE
function, and then a list of return values. CHOOSE
returns the corresponding value from the list. Let’s look at an example of this, let’s create a table for testing:
DROP TABLE IF EXISTS #Ninjas CREATE TABLE #Ninjas ( NinjaName varchar(250) ,StyleID int )
Let us now add some values to the table:
INSERT INTO #Ninjas (NinjaName, StyleID) VALUES ('Deadflow', 1) ,('Jadestroke', 3) ,('Phantom Flow', 2) ,('Death Flash', 3)
If, without having a table that resolves the styles, we want to resolve the StyleID
to a readable name, we can do so as follows
SELECT NinjaName ,StyleID ,CASE StyleID WHEN 1 THEN 'Katana' WHEN 2 THEN 'Bajutsu' WHEN 3 THEN 'Bojutsu' WHEN 4 THEN 'Shuriken' WHEN 5 THEN 'Assassin' END FROM #Ninjas
However, this syntax is not very clear and can be abbreviated with CHOOSE
. The same query would then look like the following:
SELECT NinjaName ,StyleID ,CHOOSE(StyleID, 'Katana', 'Bajutsu', 'Bojutsu', 'Shuriken', 'Assassin') FROM #Ninjas
Similarly, you can work with date columns for example, but you can run the following query on the WideWorldImporters database, which resolves the month names
SELECT OrderID ,OrderDate ,CHOOSE(MONTH(OrderDate) ,'Jan' ,'Feb' ,'Mar' ,'Apr' ,'May' ,'Jun' ,'Jul' ,'Aug' ,'Sep' ,'Oct' ,'Nov' ,'Dec') as OrderMonth FROM Sales.Orders
And this also works with other numerical values, for example we try to classify how long a customer has been a customer in the WideWorldImporters database:
SELECT CustomerID ,AccountOpenedDate ,CHOOSE(DATEDIFF(YEAR, AccountOpenedDate, GETDATE()) ,'Recent' ,'Recent' ,'Recent' ,'Average' ,'Ancient') FROM Sales.Customers
If you run this query, you will see that there are some customers who have been customers since 2013 and are therefore leaving the index. For these customers we now have to find a further classification. Fortunately, for a value that does not match any index (for example, a value less than one or greater than the number of elements in the array), CHOOSE
returns a NULL
value. Of course, we can now handle this accordingly:
SELECT CustomerID ,AccountOpenedDate ,ISNULL(CHOOSE(DATEDIFF(YEAR, AccountOpenedDate, GETDATE()) ,'Recent' ,'Recent' ,'Recent' ,'Average' ,'Ancient') ,'Jurassic') FROM Sales.Customers
Unfortunately, we still see here that the return value for the oldest customers is only “Jurassi”, the “c” is truncated, because CHOOSE
chooses the smallest possible data type as return value, to which ISNULL
sticks and truncates the return value accordingly. This is nicer if you use COALESCE
:
SELECT CustomerID ,AccountOpenedDate ,COALESCE(CHOOSE(DATEDIFF(YEAR, AccountOpenedDate, GETDATE()) ,'Recent' ,'Recent' ,'Recent' ,'Average' ,'Ancient') ,'Jurassic') FROM Sales.Customers
Now the Dinosaurs among your customers are marked accordingly, and you can continue editing.