Pivoting in Oracle

Problem #

In SQL, it is trivial to join tables together, but occasionally, you are left with many rows per person/entity/etc.

One example that I run into is student schedules. For example, it’s easy to do the following:

select
    students.name,
    schedules.period,
    courses.course
from
    students
    left join schedules on schedules.studentid = students.id
    left join courses on courses.id = schedules.courseid

NAME	PERIOD	COURSE
John Smith	1	Math
John Smith	2	Science
John Smith	3	English
Jane Doe	1	Science
Jane Doe	2	English
Jane Doe	3	Math

But what I want/need is to have one row per student and the periods across the top as. For example:

NAME	PERIOD 1	PERIOD 2	PERIOD 3
John Smith	Math	Science	English
Jane Doe	Science	English	Math

Solution #

In Oracle (and I assume there is something similar with other RDBMS) you can use the pivot clause. The following example will take the previous query and give you the desired results.

select
    name,
    'Period 1',
    'Period 2',
    'Period 3'
from
    (
        select
            students.name,
            schedules.period,
            courses.course
        from
            students
            left join schedules on schedules.studentid = students.id
            left join courses on courses.id = schedules.courseid
    )
pivot (
    max(course) for period in (1 as P1, 2 as P2, 3 as P3)
)