How to Find a List Item at a Specified Position in SQL Server

by

Starting from SQL Server 2012, you can use the T-SQL CHOOSE() function to find a list item at a specified index position within a list.

The syntax goes like this:

CHOOSE ( index, val_1, val_2 [, val_n ] )

Where index is an integer that represents the position within the list that you want to return.

Example

Here’s an example:

SELECT CHOOSE(3, 'Marge', 'Homer', 'Bart') AS 'Who is at 3?';

Result:

Who is at 3?
------------
Bart

In this case, we want to find the item at position 3. The item at position 3 is Bart.

A Database Example

Here’s an example where I match up a GenreId column with a list of genres:

SELECT 
    GenreId, 
    CHOOSE(GenreId, 'Rock', 'Jazz', 'Country') AS Genre 
FROM Genres;

Result:

GenreId  Genre  
-------  -------
1        Rock   
2        Jazz   
3        Country
4        null   
5        null   
6        null   
7        null   
8        null

Note that in this case, the null values  are returned because there were more results than were provided as an argument.