January 13, 2014

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)
)