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.