Using CASE statements in T-SQL
One of the more useful and flexible expressions in T-SQL is the CASE statement. Have you ever been asked to write a report that returns data in an unnatural form, or with slight alterations to the stored fields? Using a SELECT CASE can usually fit your needs without altering database table structure. Lets go through a couple of examples.
Customer A wants a report of all of their vendors and total dollar sums for each type of item purchased. You have a table in your database listing all orders which have fields for the vendor, the type of order, and the billing amount. Writing a query like the following will return the requested data.
SELECT Vendor, SUM(Type_A$), SUM(Type_B$)
FROM (SELECT Vendor, CASE WHEN Type = 'A' THEN Billing$ ELSE 0.0 END AS TypeA_$, CASE WHEN Type = 'B' THEN Billing$ ELSE 0.0 END AS TypeB_$ FROM Orders) AS VMatrix
GROUP BY Vendor
Customer B wants a report that determines and displays a category for orders made (category being a field type that is not stored in the database).
SELECT Vendor, Order,
CASE
WHEN Billing$ < 10.0 THEN 'Cheap' WHEN Billing$ >= 10.0 AND Billing$ < 100.0 THEN 'Average'
ELSE 'Expensive'
END AS BillingCategory
FROM Orders
Tags: case expression, case statement, examples, matrix, ms sql, query, report, subquery, t-sql
Comments
Got something to say?