SQL CHOOSE Function

The CHOOSE function returns a value from a list of values.

The value chosen is specified by an index.

The index starts at 1, not 0.

Example

#

This example return the third value from a list of values.

SELECT CHOOSE(3, 'HTML', 'CSS', 'SQL', 'JavaScript') AS Chosen
Result:  1 record
Chosen
SQL

Syntax

Syntax of the CHOOSE function.

CHOOSE(index, value1, value2, ..., valuen)

index -- an integer that represents the index (i.e. position).

values -- a comma separated list of values to look up.

More Examples

CHOOSE. with LOOKUP

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List sales by labeled quarter for the year 2013.
SELECT CHOOSE(DATEPART(quarter, OrderDate), 
              'Early', 'Mid', 'Late', 'Last') AS 'Quarter',
       SUM(TotalAmount) AS 'Total Sales'
  FROM [Order]
 WHERE YEAR(OrderDate) = 2013
 GROUP BY DATEPART(quarter, OrderDate)
Result:  4 records
Quarter Total Sales
Early 147879.90
Mid 151611.09
Late 165179.64
Last 193718.12

Note: The list of values in the SQL query effectively represents a lookup table.


You may also like



Guides


vsn 3.1