Using CASE WHEN in MS SQL
In Ms Sql the CASE WHEN structure is often used to set the value of a column based on values in other columns or variables.
The value with TRUE is written after the WHEN value and there must be at least one WHEN value.
Let's examine the table named PRODUCTS below..
| NAME | PRICE |
|---|---|
| Milk | 6.0 |
| Sugar | 3.0 |
| Apple | 3.0 |
| Water | 1.5 |
| Salt | 2.0 |
| Pencil | 4.0 |
| Oil | 20.0 |
| Cheese | 15.0 |
In our query, we will use the CASE WHEN structure to show a new column of conditions that we will create according to our PRICE_COMMENT column.
SELECT NAME,PRICE, CASE WHEN PRICE > 10 THEN 'Expensive' WHEN PRICE > 5 THEN 'Normal' WHEN PRICE > 1 THEN 'Cheap' END AS PRICE_COMMENT FROM PRODUCTSIn the query we wrote, we created a new column called PRICE_COMMENT (it was not physically created in the table, just to show it in the query) and we showed values in this column according to our conditions in the CASE WHEN structure.
The result that the query will return us will be as follows..
| NAME | PRICE | PRICE_COMMENT |
|---|---|---|
| Milk | 6.0 | Normal |
| Sugar | 3.0 | Cheap |
| Apple | 3.0 | Cheap |
| Water | 1.5 | Cheap |
| Salt | 2.0 | Cheap |
| Pencil | 4.0 | Cheap |
| Oil | 20.0 | Expensive |
| Cheese | 15.0 | Expensive |