Pivot Tables Using SQL

Posted on May 12, 2017 · 2 mins read

Pivot tables using sql

Simple example

This is the basic syntax of the Oracle PIVOT clause:

SELECT
    select_list
FROM  table_name
PIVOT [XML] (
        pivot_clause
        pivot_for_clause
        pivot_in_clause
);

Example:

SELECT *
FROM courses_view
PIVOT(
   COUNT(course_id)
   FOR academic_desipline
   IN ( 'Law and Social' Law_and_Social,  -- this is an alias
        'Engineering',
        'Education',
        'Health'
      )
)
ORDER BY course_name;

Lets add aggregaated colomns

We can use the ```pivot_clause`` to return the number of

SELECT *
FROM graduates_students_view
PIVOT(
   COUNT(student_id),
   AVG(grade)
   FOR academic_desipline
   IN ( 'Law and Social' Law_and_Social, 
        'Engineering',
        'Education',
        'Health'
      )
)
ORDER BY status;

The pivot_in_clause

We can write this clause as list as seen above, but  also as a subquery using the XML option - and then parse the result:

SELECT *
FROM graduates_students_view
PIVOT XML (
   COUNT(student_id),
   AVG(grade)
   FOR academic_desipline
   IN (
        SELECT REPLACE(desipline_name, ' ', '_')
        FROM academic_desiplines_view
      )
)
Next Post →