pivot

Posted on November 2, 2013 Updated on November 4, 2013

Ever saw oracle’s official documentation on the SQL PIVOT ? Couldn’t find the matching documentation for it, and no examples? I have turned the PIVOT inside-out, and will share you what it does.

Starting with pages of unrelated data points

SELECT
student,
subject,
minor,
quarter,
score,
score_max
FROM metric

output

student	subject	minor	quarter	score	score_max
Jeff	Math	Algebra	1	14	20
Jeff	Math	Trigonometry	1	14	20
Jeff	English	Grammar	2	9	10
Jeff	English	Literature	2	9	10
…

This table basically stores for each student his scores on a couple of subjects, further subdivided into minor subject, and for each individual quarter of the year.
Now, lets try to apply a full fledged PIVOT, including all whistles and bells …

SELECT * FROM (
  SELECT student, subject, minor, score, score_max FROM metric
) PIVOT (
  COUNT(DISTINCT quarter) AS "CNT",
  SUM(score) AS num,
  SUM(score_max) AS den
  FOR (subject,minor) IN (
    ('Math','Trigonometry') AS "M_TRI",
    ('Math','Algebra') AS "M_ALG",
    ('English','Grammar') AS "E_GRA",
    ('English','Literature') AS "E_LIT" )
  )

And again the output … maybe to some surprises …

After the Pivot

student	m_tri_cnt	m_tri_num	m_tri_den	m_alg_cnt	m_alg_num	m_alg_den	e_gra_cnt	e_gra_num	e_gra_den	e_lit_cnt	e_lit_num	e_lit_den
Jeff	2	28	40	2	14	20	4	69	100	3	356	400
Mieke	3	38	40	2	18	20	4	69	100	4	301	400
…

Ok, we all expected rows be turned into columns based on some determinator. But a lot more happened, that is obvious. I will list out –

• FOR (determinators) identifies the determinators used to identify the pivoted columns, in this case a combination of two columns (subject,minor).
• IN (...) lists out any combination of the determinators, and the associated alias (the new column name).
• The first part within the PIVOT (aggregators FOR ...), the aggregators (MIN,MAX, COUNT, …). These aggregators basically describe a method to be used to collapse multiple rows into one. In the simplified case, the chosen aggregation method wont matter, But in this example it does. Any column not used in the aggregator sector, will be part of an implicit GROUP BY. When providing multiple aggregators, you need to associate each aggregator with it’s own alias. This alias will be concatenated at the end of the alias provided for the determinator alias.

English is a very ambiguous medium to try to explain something like this, but this is why the provided examples should shed light on the obscurities. Don’t forget to keep the Oracle SQL Reference Guide handy.
We started with the most complex example. Now to show that it does not need to be always that verbose, we will simplify this example. We will assume:

• All scores are 100-based, and to come to a total score we can just SUM(...)
• There are no minor subjects anymore
• No quarters either, just a list of students and their score for each main subject

SELECT
  student,
  subject,
  score
FROM metric

output

student	subject	score
Jeff	Math	80
Jeff	English	93
…

Now, if we want to pivot this, it becomes much more simpler, and less verbose:

SELECT *
FROM
  metric
  PIVOT (
    SUM(score) FOR (subject) IN ('Math','English')
  )

What simplified this is the following:

• With a single aggregator, no more need for a separate alias suffix.
• Single determinator.
• The list of determinator combinations does not have an alias defined either. However Columns will appear literally as the expression, showing ‘Math’, including the single quotes.
• The pivoted query is now just the table itself, no subquery.

output

student	‘Math’	‘English’
Jeff	212	534
Joe	365	653
John	456	543
Jo-Ann	383	645
…