CASE WHEN

What is CASE WHEN?

Last week you already got to know the IIF-statement and in the course of this you also saw CASE WHEN-statements directly. Today we want to take a closer look at the CASE WHEN statements. Like IIF, CASE WHEN is a construct from the realm of logic functions that allow you to react to certain values.

What can you use CASE WHEN for?

Like the IIF function, you can also use CASE WHEN to add a different value to the result depending on the value of a column:

SELECT 
     StockItemTransactionID
    ,StockItemID
    ,TransactionTypeName = 
        CASE TransactionTypeID 
        WHEN 10 THEN 'Stock Issue' 
        WHEN 11 THEN 'Stock Receipt' 
        WHEN 12 THEN 'Stock Adjustment at Stocktake' 
        ELSE 'Unknown' END
    ,CustomerID
    ,InvoiceID
    ,SupplierID
    ,PurchaseOrderID
FROM Warehouse.StockItemTransactions 

Now we can write this statement as above, but we could also paraphrase it and write out the comparison operation which is implicit here:

SELECT 
     StockItemTransactionID
    ,StockItemID
    ,TransactionTypeName = 
        CASE  
        WHEN TransactionTypeID= 10 THEN 'Stock Issue' 
        WHEN TransactionTypeID= 11 THEN 'Stock Receipt' 
        WHEN TransactionTypeID= 12 THEN 'Stock Adjustment at Stocktake' 
        ELSE 'Unknown' END
    ,CustomerID
    ,InvoiceID
    ,SupplierID
    ,PurchaseOrderID
FROM Warehouse.StockItemTransactions 

The result of this query is, of course, the same as the first variant. But what happens if we want to aggregate over the derived values? Can we group by a column that was processed in a CASE statement? Yes, in this case it is possible, as the following query shows:

SELECT 
     TransactionTypeName = 
        CASE TransactionTypeID 
        WHEN 10 THEN 'Stock Issue' 
        WHEN 11 THEN 'Stock Receipt' 
        WHEN 12 THEN 'Stock Adjustment at Stocktake' 
        ELSE 'Unknown' END
    ,AVG(Quantity)
FROM Warehouse.StockItemTransactions 
GROUP BY TransactionTypeID

This no longer works if several columns are used in your CASE statement:

SELECT 
     TransactionTypeName = 
         CASE  
         WHEN TransactionTypeID=10 AND Quantity > 0 THEN 'Pos. Stock Issue' 
         WHEN TransactionTypeID=11 AND Quantity > 0 THEN 'Pos. Stock Receipt' 
         WHEN TransactionTypeID=12 AND Quantity > 0 THEN 'Pos. Stock Adjustment' 
         ELSE 'Unknown' END
    ,AVG(Quantity)
FROM Warehouse.StockItemTransactions 
GROUP BY TransactionTypeID

This query gives an error, if we want to use such a query, all we have to do is adjust the GROUP BY and use our CASE statement here too:

SELECT
     TransactionTypeName = 
         CASE  
         WHEN TransactionTypeID=10 AND Quantity > 0 THEN 'Pos. Stock Issue' 
         WHEN TransactionTypeID=11 AND Quantity > 0 THEN 'Pos. Stock Receipt' 
         WHEN TransactionTypeID=12 AND Quantity > 0 THEN 'Pos. Stock Adjustment' 
         ELSE 'Unknown' END
    ,AVG(Quantity)
FROM Warehouse.StockItemTransactions 
GROUP BY
    CASE  
        WHEN TransactionTypeID=10 AND Quantity > 0 THEN 'Pos. Stock Issue' 
        WHEN TransactionTypeID=11 AND Quantity > 0 THEN 'Pos. Stock Receipt' 
        WHEN TransactionTypeID=12 AND Quantity > 0 THEN 'Pos. Stock Adjustment' 
        ELSE 'Unknown' END

And as a note: even if the conditions in the CASE statement are identical, the output values must also be identical. The following statement therefore fails:

SELECT 
     TransactionTypeName = 
         CASE  
         WHEN TransactionTypeID=10 AND Quantity > 0 THEN 'Pos. Stock Issue' 
         WHEN TransactionTypeID=11 AND Quantity > 0 THEN 'Pos. Stock Receipt' 
         WHEN TransactionTypeID=12 AND Quantity > 0 THEN 'Pos. Stock Adjustment at Stocktake' 
         ELSE 'Unknown' END
    ,AVG(Quantity)
FROM Warehouse.StockItemTransactions 
GROUP BY
    CASE  
        WHEN TransactionTypeID=10 AND Quantity > 0 THEN 'Case 1' 
        WHEN TransactionTypeID=11 AND Quantity > 0 THEN 'Case 2' 
        WHEN TransactionTypeID=12 AND Quantity > 0 THEN 'Case 3' 
        ELSE 'Unknown' END

Like IIF, CASE WHEN is one of the important statements for modifying data and adjusting the output in the result set. Unlike the IIF function, however, the CASE WHEN construct is included in the ANSI SQL standard and works on all versions of SQL Server you might encounter in the wild as well as most other relational database systems.

References

• Documentation from Microsoft