MySQL Pivot with Examples

Previous Page
Next Page

A Pivot table is to transpose a table i.e. change rows to columns and vice versa. The concept is the same as the Pivot feature in Microsoft excel. MySQL does not support the PIVOT feature. However, we can emulate the behavior of the PIVOT by using the case statement.

The basic syntax of the case statement is as follows;

CASE value
   WHEN value1 THEN result1
   WHEN value2 THEN result2
   …
   [ELSE else_result]
END

We will look at two examples of pivoting in MySQL using the Case statement. 

Example 1:

select f.title, f.release_year,fl.price AS 'Price in USD',
CASE
    when fl.price BETWEEN 0 AND 2.5 THEN 'Discount Price'
    when fl.price BETWEEN 2.6 AND 4.99 THEN 'MRP'
    when fl.price > 4.99 THEN 'Premium'
END as 'Price_Range'
FROM film f inner join film_list fl ON f.film_id = fl.FID;

Example 2:

This is to label each record on the basis of the value in the rating column.

select title, description,rental_rate,
CASE rating
    WHEN 'G' THEN 'ALL'
    WHEN 'PG' THEN 'Parental Guidance'
    WHEN 'PG-13' THEN 'TEEN Movies'
    WHEN 'R' THEN 'ADULTS'
    WHEN 'NC-17' THEN 'ADULTS'
END AS 'Audience'
From film;

Previous Page
Next Page