CHOOSE

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.

References

• Documentation from Microsoft