Dr Who and Oracle SQL Pivot

Dr Who recently celebrated it’s 60th Anniversary and the BBC marked the occasion by making all episodes since 1963 available to stream.
This has given me the opportunity to relive those happy childhood Saturday afternoons spent cowering behind the sofa watching Tom Baker
take on the most fiendish adversaries that the Costume department could conjure up in the days before CGI.
Like the Doctor, I too am I time traveller. OK, so I can only move forwards through time, but I do have some knowledge of the future.
For example, present me knows that future me is an idiot who will undoubtedly forget of the syntax for the Pivot command in Oracle SQL next time he wants to pivot some VARCHAR2 columns.
So if you are future me, this is for you. You owe me a drink…

I have a simple table :

create table doctor_who
(
    incarnation varchar2(50),
    actor varchar2(100)
)
/

which I’ve populated with the following records :

select incarnation, actor 
from doctor_who;

INCARNATION          ACTOR                         
-------------------- ------------------------------
1st                  William Hartnell              
2nd                  Patrick Troughton             
3rd                  Jon Pertwee                   
4th                  Tom Baker                     
5th                  Peter Davison                 
6th                  Colin Baker                   
7th                  Sylvester McCoy               
8th                  Paul McGann                   
9th                  Christopher Eccleston         
10th                 David Tennant                 
11th                 Matt Smith                    
12th                 Peter Capaldi                 
13th                 Jodie Whittaker               
14th                 David Tennant                 
15th                 Ncuti Gatwa                   
Fugitive Doctor      Jo Martin                     
War Doctor           John Hurt                     

17 rows selected. 

If I want a query to return a single row which lists selected incarnations, I can do this :

select * 
from 
(
    select 
        incarnation,
        actor
    from doctor_who
)
pivot
(
    max(actor)
    for incarnation in 
    (
        '1st' as first,
        '4th' as fourth,
        '10th' as tenth,
        'War Doctor' as war,
        'Fugitive Doctor' as fugitive
    )
)
/

Which returns :

FIRST            FOURTH      TENTH           WAR           FUGITIVE            
-------------    ----------- --------------- ------------- -------------------
William Hartnell Tom Baker   David Tennant   John Hurt     Jo Martin           

The first thing to notice here is that the PIVOT clause insists on an aggregate function for the column that holds the data you want to display.
Failure to use one results in :

ORA-56902: expect aggregate function inside pivot operation

Fortunately, the MAX function works happily with VARCHAR2 and doesn’t impact the data in ACTOR, because there’s only one value for each incarnation in our example.

Unsurprisingly, the subquery needs to include both the columns to pivot and those containing the values we want to display.

If you think having a list of Doctor Who Actors means that we already have quite enough stars, you can tweak the query so that we name the columns we’re querying in the SELECT clause.
Note that you can only use columns that you’ve defined in the PIVOT clause ( although you don’t have to use all of them) :

select fourth, tenth, war, fugitive
    -- Not selecting "first", although it's defined in the PIVOT clause
    -- may be useful when developing/debugging
from 
(
    select 
        incarnation,
        actor
    from doctor_who
)
pivot
(
    max(actor)
    for incarnation in 
    (
        '4th' as fourth,
        '10th' as tenth,
        'War Doctor' as war,
        'Fugitive Doctor' as fugitive,
        '1st' as first
    )
)
/

FOURTH               TENTH                WAR                  FUGITIVE            
-------------------- -------------------- -------------------- --------------------
Tom Baker            David Tennant        John Hurt            Jo Martin           

Finally, as you’d expect, the PIVOT CLAUSE does not affect the number of rows read to process the query.
For that, we still need a good old fashoined WHERE CLAUSE in the subquery.

On my OCI 19c Free Tier instance, the plan for the above statement is :

If we add a WHERE CLAUSE. however :

select fourth, tenth, war, fugitive
from 
(
    select 
        incarnation,
        actor
    from doctor_who
    where incarnation in ('4th', '10th', 'War Doctor', 'Fugitive Doctor', '1st')
)
pivot
(
    max(actor)
    for incarnation in 
    (
        '4th' as fourth,
        '10th' as tenth,
        'War Doctor' as war,
        'Fugitive Doctor' as fugitive,
        '1st' as first
    )
)
/

Now that’s done, I can watch the next episode of Dr Who and the Sticky-back Plastic Monster. Unfortunately, I no longer fit behind the sofa.